Average if X amount of rows

[skate1991]

HI All,

I need to do an average if based on one criteria. The tricky part is that i need to do the average for the first 70,80,90&100% of the data set.

I have worked out how many rows I need to go down for example 235 for 70% 268 for 80% the number of rows they have to go down are in cell E1,E2,E3&E4. the numbers I want to average are in cell A1:A50 the criteria range is in cell B1:B50 and the criteria is in cell E5

I just need the formula to take these into consideration.

Any Ideas

[Glenn Kennedy]

It's a bit hard to visualise. Can you pst a sample Excel sheet?

[skate1991]

Hi Glenn,

Please find an example attached.

In col A is the data I want to average

in cells B is the criteria column
In Col E are the % of the total we want to average
in F is the number of data points we want to average
In Col G this is where I need the average
Currently there not in order if we could have a formula which takes that into consideration that would be amazing if not I can sort these from lowest to highest
I hope this explains it a bit better. Please let me know if you need anything else,
Thanks

[Glenn Kennedy]

There are probably lots of ways to do this. Here's one, using an Array Formula, which must be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

You can delete the intermediate steps in G & H. I've just left them in for now, in case I have to modify things.

[pepe74287]

Another way to go:

=AVERAGEIF(OFFSET($B$2,0,0,SMALL(IF($B$2:$B$50=$D$1,ROW($B$2:$B$50)),$F2)-1,1),$D$1,$A$2:$A$50)

Still array formula, so needs to be confirmed with CTRL+SHIFT+ENTER.