Average Of A Range Based On Two Conditions

[VGPOP]

I know that I have to use an array formula for this, but I am not getting the correct results.

This is what I have:

Results; Scores
F25 = 5; I25 = 8.5
F26 = 4; I26 = 9.0
F27 = 8; I27 = 8.0
F28 = 3; I28 = 8.0
F29 = 1; I29 = 7.0
F30 = 5; I30 = 7.0
F31 = 9; I31 = 8.5
F32 = 7; I32 = 8.5
F33 = 4; I33 = 9.5
... ; ...
... ; ...
F64; I64

I want to calculate the average of the LAST 3 RESULTS that are less than equal to 8.5 but greater than or equal to 8.0.

(I25:I64 <= 8.5) (I25:I64 >= 8.0)

So, in the above example the result is 6.33 = [f32 (7) + f31 (9) + f28 (3)]

Anyway I can accomplish this?

Thanks in advanced.

 

[barry houdini]

Try

=AVERAGE(IF(ROW(F25:F64)>=LARGE(IF(I25:I64>=8,IF(I25:I64<=8.5,ROW(F25:F64))),3),IF(I25:I64>=8,IF(I25:I64<=8.5,F25:F64))))

confirmed with CTRL+SHIFT+ENTER

 

[VGPOP]

Try

=AVERAGE(IF(ROW(F25:F64)>=LARGE(IF(I25:I64>=8,IF(I25:I64<=8.5,ROW(F25:F64))),3),IF(I25:I64>=8,IF(I25:I64<=8.5,F25:F64))))

confirmed with CTRL+SHIFT+ENTER

Works beautiful, thanks!

 

[Yogi Anand]

Hi VGPOP:

another ...

array formula: =AVERAGE(IF((I25:I64>=8)*(I25:I64<=8.5)*ROW(I25:I64)>LARGE((I25:I64>=8)*(I25:I64<=8.5)*ROW(I25:I64),4),F25:F64))

 

[facethegod]

This may also do the trick...


=AVERAGE(IF(ISNUMBER(MATCH(ROW(F25:F33)-ROW(F25)+1,LARGE(IF(G25:G33>=8,IF(G25:G33<=8.5,ROW(G25:G33)-ROW(G25)+1)),{1,2,3}),0)),F25:F33))

confirmed w/ Ctrl + Shift + Enter