# Finding the average of top and bottom 10% of a dataset

1. ## Finding the average of top and bottom 10% of a dataset

Hello,

I have a large dataset and I am trying to find the average of the top 20% and bottom 20% for each city. I am attaching the sample data and you can see under columns F and G the formulas that I tried to use to find this however it will not work.

The formulas I am using are below:
For average of the top 20% for a particular city in the list =AVERAGEIFS(\$A:\$A,\$D2,\$B:\$B,\$B:\$B,LARGE(\$B:\$B,ROW(INDIRECT("1:20"))))
For average of the bottom 20% for a particular city in the list =AVERAGEIFS(\$A:\$A,\$D2,\$B:\$B,\$B:\$B,SMALL(\$B:\$B,ROW(INDIRECT("1:20"))))

When I run the formulas like this by itself then I get a #SPILL error message, and when I do Ctrl+Shift+Enter then I get a #VALUE error message.

Any light you can shed on how to do this correctly is greatly appreciated!!  Register To Reply

2. ## Re: Finding the average of top and bottom 10% of a dataset

almost there. 20% (in this case) is top 3.2 values (15*.2)
so maybe this?
=AVERAGEIF(\$B\$2:\$B\$16,">="&LARGE(\$B\$2:\$B\$16,COUNT(B:B)*0.2))
change LARGE to SMALL for the bottom range  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 