Solution to Return top 5 values based on a criterion

Almir's picture

Solution for: http://excelexperts.com/return-top-5-values-based-criterion

In brief: you need Category List re-arranged, additional column in "Average Rank" sheet and named ranges for newly inserted column and all months respectively.

In detail: I re-arranged Category List and made a new one on sheet "Category_New".
Then I inserted a column on sheet "Average Rank" containing type (Composite, Standalone etc.).
Named ranges were created for march and april 2013 (containing values).

Finally, array formula calculates Top 5. Example for February 2013 - "Composite":

=LARGE(IF(Typess=$P$4;febr2013;"");1)

It returns the first largest value in column of February 2013 where "Type" is "Composite".
Type is in B column on sheet "Average Rank", and "Febr2013" is a column containing values for February 2013. $P$4 is a fixed cell value ("Composite") on sheet "Average Rank".

AttachmentSize
Top sports apps.xlsx255.12 KB