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