# Average Of A Range Based On Two Conditions

#### VGPOP

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

Last edited:

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### barry houdini

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
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

##### Well-known Member
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

Replies
0
Views
179
Replies
1
Views
1K
Replies
11
Views
1K
Replies
5
Views
223
Replies
3
Views
120

1,190,883
Messages
5,983,387
Members
439,841
Latest member
goodwillhunting

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back