Top 5 Results

Robert Gallegos

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:

1. How to limit the report to the top 5
2. How to plot the results showing only the top 5

Paul Newcome

Are you pulling data from multiple sheets?

Robert Gallegos

No Single sheet

Paul Newcome

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.

Robert Gallegos

Excellent option. Thank-you!

Paul Newcome

Happy to help. 👍️

asish lamichhane

What if i want to see top 5 by each status? how do i get it to work?

Paul Newcome

@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)

Seth Morth

Paul Newcome

Oct 7, 2021

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.

Paul! This is AWESOME! It just saved my bacon - thank you!

😀🥓