Calculating Averages based on criteria
 

I'm trying to calculate an average based on some criteria in the adjacent
columns. The user has to first identiy Y or N and then determine if the
average should be calculated based on 2, 3 or 4 years.

Is there a formula that would only averge the correct number of cells 2, 3
or 4 years depending on what the user picked and divide by the right #?

Calculating Averages based on criteria
 

Hi,
try

=AVERAGEIF(E6:E9,"Y",D6:D9)

change the range to fit your needs

if this helps please click yes, thanks

"Kgov" wrote:

I'm trying to calculate an average based on some criteria in the adjacent
columns. The user has to first identiy Y or N and then determine if the
average should be calculated based on 2, 3 or 4 years.

Is there a formula that would only averge the correct number of cells 2, 3
or 4 years depending on what the user picked and divide by the right #?

Calculating Averages based on criteria
 

If you mean your data as below; then try the below formula. Please note that
this is an array formula. Within the cell in edit mode (F2) paste this
formula and press Ctrl+Shift+Enter to apply this formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula}"

Col A Col B Col C
Y 3 =AVERAGE(IF(A1:A5="Y",B1:B5))
N 3
Y 6
N 5


If this post helps click Yes
---------------
Jacob Skaria


"Kgov" wrote:

I'm trying to calculate an average based on some criteria in the adjacent
columns. The user has to first identiy Y or N and then determine if the
average should be calculated based on 2, 3 or 4 years.

Is there a formula that would only averge the correct number of cells 2, 3
or 4 years depending on what the user picked and divide by the right #?

Calculating Averages based on criteria
 

I should have mentioned. I don't have averageif. (not sure what
version...new job)

"Jacob Skaria" wrote:

If you mean your data as below; then try the below formula. Please note that
this is an array formula. Within the cell in edit mode (F2) paste this
formula and press Ctrl+Shift+Enter to apply this formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula}"

Col A Col B Col C
Y 3 =AVERAGE(IF(A1:A5="Y",B1:B5))
N 3
Y 6
N 5


If this post helps click Yes
---------------
Jacob Skaria


"Kgov" wrote:

I'm trying to calculate an average based on some criteria in the adjacent
columns. The user has to first identiy Y or N and then determine if the
average should be calculated based on 2, 3 or 4 years.

Is there a formula that would only averge the correct number of cells 2, 3
or 4 years depending on what the user picked and divide by the right #?

Calculating Averages based on criteria
 

never mind I see you didn't use averageif.

Is there away to have the formula average 2, 3 or 4 cells depending on the
number of years the user selects? It would always start from the last three
years so D,C,B.


A B C D E F G
10 12 8 15 Y 3 11.6 Avg of 3 years

"Jacob Skaria" wrote:

If you mean your data as below; then try the below formula. Please note that
this is an array formula. Within the cell in edit mode (F2) paste this
formula and press Ctrl+Shift+Enter to apply this formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula}"

Col A Col B Col C
Y 3 =AVERAGE(IF(A1:A5="Y",B1:B5))
N 3
Y 6
N 5


If this post helps click Yes
---------------
Jacob Skaria


"Kgov" wrote:

I'm trying to calculate an average based on some criteria in the adjacent
columns. The user has to first identiy Y or N and then determine if the
average should be calculated based on 2, 3 or 4 years.

Is there a formula that would only averge the correct number of cells 2, 3
or 4 years depending on what the user picked and divide by the right #?

Calculating Averages based on criteria
 

Average is just Sum divided by count, so use:

=sumif(a1:a5,"Y",b1:b5)/countif(a1:a5,"Y",b1:b5)

Regards,
Fred.

"Kgov" wrote in message
...
I should have mentioned. I don't have averageif. (not sure what
version...new job)

"Jacob Skaria" wrote:

If you mean your data as below; then try the below formula. Please note
that
this is an array formula. Within the cell in edit mode (F2) paste this
formula and press Ctrl+Shift+Enter to apply this formula. If successful
in
'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

Col A Col B Col C
Y 3 =AVERAGE(IF(A1:A5="Y",B1:B5))
N 3
Y 6
N 5


If this post helps click Yes
---------------
Jacob Skaria


"Kgov" wrote:

I'm trying to calculate an average based on some criteria in the
adjacent
columns. The user has to first identiy Y or N and then determine if the
average should be calculated based on 2, 3 or 4 years.

Is there a formula that would only averge the correct number of cells
2, 3
or 4 years depending on what the user picked and divide by the right #?

Calculating Averages based on criteria
 

With your sample data in row1 try the below formula in cell G1

=IF(E1="Y",AVERAGE(INDIRECT(ADDRESS(ROW(A1),COLUMN (D1)-F1+1) &":D1")),"")

If this post helps click Yes
---------------
Jacob Skaria


"Kgov" wrote:

never mind I see you didn't use averageif.

Is there away to have the formula average 2, 3 or 4 cells depending on the
number of years the user selects? It would always start from the last three
years so D,C,B.


A B C D E F G
10 12 8 15 Y 3 11.6 Avg of 3 years

"Jacob Skaria" wrote:

If you mean your data as below; then try the below formula. Please note that
this is an array formula. Within the cell in edit mode (F2) paste this
formula and press Ctrl+Shift+Enter to apply this formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula}"

Col A Col B Col C
Y 3 =AVERAGE(IF(A1:A5="Y",B1:B5))
N 3
Y 6
N 5


If this post helps click Yes
---------------
Jacob Skaria


"Kgov" wrote:

I'm trying to calculate an average based on some criteria in the adjacent
columns. The user has to first identiy Y or N and then determine if the
average should be calculated based on 2, 3 or 4 years.

Is there a formula that would only averge the correct number of cells 2, 3
or 4 years depending on what the user picked and divide by the right #?