Average if(s)

Bioinfo

New Member
Joined
Oct 7, 2014
Messages
2
Hi All,
I wanted to calculate averages in column D between a range of values given in column B and C from the values in A.
e.g. in the following, I wanted to average of 6,7,9 (D2,D3,D4) based on B1 (i.e.10) and C1 (i.e. 30) which are given in A2 to A4.
A B C D
1
10303
1050606
20901007
309
408
504
606
702
805
906
1003

<tbody>
</tbody>


Thank you so much.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,181
In E1:
=AVERAGE(OFFSET(INDEX(D:D,MATCH(B1,A:A,0)),0,0,MATCH(C1,A:A,0)-MATCH(B1,A:A,0)+1,1))
fill down to E3.
 

Bioinfo

New Member
Joined
Oct 7, 2014
Messages
2
In E1:
=AVERAGE(OFFSET(INDEX(D:D,MATCH(B1,A:A,0)),0,0,MATCH(C1,A:A,0)-MATCH(B1,A:A,0)+1,1))
fill down to E3.

Thanks a lot Bob,
It works excellent. But I wanted the average values in E below the range, may be in a new row. Which means, is it possible to have the first average (A2 to A4) in E5, second average subsequently in E9 and so on. I have a large dataset.
Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,314
Members
414,053
Latest member
Dual Showman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top