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!!
Bookmarks