Calculating Averages based on criteria

[Kgov]

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 #?

 

[Eduardo]

Hi,
try

=AVERAGEIF(E6:E9,"Y",D69)

change the range to fit your needs

if this helps please click yes, thanks

 

[Jacob Skaria]

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

 

[Kgov]

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

 

[Kgov]

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

 

[Fred Smith]

Average is just Sum divided by count, so use:

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

Regards,
Fred.

 

[Jacob Skaria]

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) &"1")),"")

If this post helps click Yes