Top 5 Results
I want to plot the top 5 results from a report. I ran a report to see all the quantities and the person associated with the qty. I then sorted from high to low to see who had the top 5 highest quantity. But wasn't able to figure out:
- How to limit the report to the top 5
- How to plot the results showing only the top 5
Answers
-
Are you pulling data from multiple sheets?
-
No Single sheet
-
My suggestion would be a hidden helper column (checkbox) with the following formula:
=IF([Number Column]@row>= LARGE([Number Column]:[Number Column], 5), 1)
This will check the box for the top 5. You can then filter your report based on this box being checked.
-
Excellent option. Thank-you!
-
Happy to help. 👍️
-
What if i want to see top 5 by each status? how do i get it to work?
-
@asish lamichhane You would replace the LARGE function with a LARGE/COLLECT
LARGE([Number Column]:[Number Column], 5)
turns into
LARGE(COLLECT([Number Column]:[Number Column], [Status Column]:[Status Column], @cell = [Status Column]@row), 5)
-
Paul! This is AWESOME! It just saved my bacon - thank you!
😀🥓
Help Article Resources
Categories
Check out the Formula Handbook template!
