Pull Top 5 From a List

Hello,

I was wondering if it were possible to build a formula that would consistently pull the top 5 from a list?

I am building a dashboard and one of the requests was to have the top 5 issues consistently listed out of a total possible 100 choices.

I already broke out the totals using the COUNTIF formula. It is also set to where the top 5 could change at any moment based on the issue.

Any help would be greatly appreciated. If you need any further information, let me know, or I could even share the sheet with you.

Thank You,

Dustin

Find more posts tagged with

Accepted answers

Genevieve P.

Hi @Dustin Whitehead

The way I would personally do this is to have a helper column which would identify the total rank of each row in this sheet, which would provide a number for each of your top 5 rows (using Andrée's suggestion of a RANKEQ formula).

Then you can use a Report to only pull the top 5 rows into the Report, and use the Report as the source for your Chart Widget.

This means that as numbers shift and change, the helper rank numbers 1 - 5 will become associated with different rows, the Report will update, in turn updating your Dashboard chart. Does that make sense?

Here's an example of the formula you'd use in the helper checkbox column:

=RANKEQ([Number Column]@row, [Number Column]:[Number Column], 0)

Here's how you'd set up the Report Filter criteria:

Screen Shot 2021-07-20 at 10.11.23 AM.png

Let me know if you still need help and where you're getting stuck, I'd be happy to provide more screen captures/steps!

Cheers,

Genevieve

Techspan CT

@Dustin Whitehead You dont need to use RANKEQ at all... if you already have your percentage or counts all you need is a report that is sorted from highest to lowest. On the dashboard you use the "Report" widget and set it to only show 5 rows. This will always display the highest 5 percentages (or counts if that is the more important data).

The challenge is if you need to show it in a chart as you can't restrict a chart to show only the top 5. If this is what you need then you will have to use the RANKEQ formula to get the top 5 and set up the report in the way @Genevieve P described.

Also just so you know - the RANKEQ formula will return the same ranking number if you have more than one item that have the same count...! This means that if you have more than 5 items with the same count you would end up with 6 or 7 items with a rank less than 5.

A workaround i discovered on another post is to use this formula to only return unique ranking numbers.

=RANKEQ(Count@row, Count:Count) + COUNTIF(Count$1:Count@row, RANKEQ(@cell, Count:Count) = RANKEQ(Count@row, Count:Count)) - 1 where "Count" is the column that you are ranking.

Hope this helps! 😀

All comments

Bassam Khalil

Hi @Dustin Whitehead

Hope you are fine, did you mean the recent 5 updated rows, or you have a deferent criteria that define the top 5?

Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Andrée Starå

Hi @Dustin Whitehead

I hope you're well and safe!

Two options come to mind.

Report
Formula using the RANK function

Would any of those options work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Dustin Whitehead

Hello ...@Bassam Khalil and @Andrée Starå

So, this is just a sample of the sheet but what I need is to have the top five (as in the 5 largest numbers) consistently present themselves so that I can create a graph on a Dashboard that constantly shows the top 5 largest categories as they change ... let me know if you need any more information.

Genevieve P.

Hi @Dustin Whitehead

Then you can use a Report to only pull the top 5 rows into the Report, and use the Report as the source for your Chart Widget.

Here's an example of the formula you'd use in the helper checkbox column:

=RANKEQ([Number Column]@row, [Number Column]:[Number Column], 0)

Here's how you'd set up the Report Filter criteria:

Let me know if you still need help and where you're getting stuck, I'd be happy to provide more screen captures/steps!

Cheers,

Genevieve

Techspan CT

A workaround i discovered on another post is to use this formula to only return unique ranking numbers.

=RANKEQ(Count@row, Count:Count) + COUNTIF(Count$1:Count@row, RANKEQ(@cell, Count:Count) = RANKEQ(Count@row, Count:Count)) - 1 where "Count" is the column that you are ranking.

Hope this helps! 😀

Techspan CT

This screenshot shows what i mean by the RANKEQ formula:

Genevieve P.

@Techspan CT

Thank you so much for commenting on this post - these are really great insights!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.