Formula Help - Rank to be able to use on reports

Hi All,

Just wondering if anyone can help me with your wisdom!

From the list of assets below, I need to have top 5 or top 10 of most ordered as well as less ordered (please note there is a repetition of the same asset name throughout the sheet) and there's 2 x different categories: social and TV which need to be ranked in top 5/10 each. I tried the rank formula but didn't give me anything,

=RANKEQ([Number of Orders by Spot With Duplicated Tags]@row, [Number of Orders by Spot With Duplicated Tags]:[Number of Orders by Spot With Duplicated Tags], [TV/SOCIAL]:[TV/SOCIAL], ="social")

The closest I'm able to get was by creating a separate metric sheet (which is not ideal) but i have the amount of times that data shows up in the same cell, so it looks like the below instead of showing unique files. The index match doesn’t seem to work, because some Assets have been ordered the exact same amount of times, so it only displays the first one!

using this formula: =JOIN(COLLECT({Sheet – Range 3}, {Sheet – Range 4}, [Column3]@row, { Sheet – Range 5}, <>" Spots"), " ")

I have the same problem for the least amount of assets ordered + it also shows the 0's which is not ideal!

Anyone knows what formula i should to display the top 10 rank if the assets are "social" in the same sheet as my raw data and the same for the least one? I am trying to create some reports to only show me the most used and the least used and I wanted to automate this instead of me having to manually check which ones are the top 5 of that specific asset, so need a column with the ranking so I can then filter

Thank you so much!

Find more posts tagged with

Formulas

ranks

Accepted answers

Georgie

Hi @Mariana Fortuna,

The RANKEQ function is used to return the position of a number in a range of numbers, so for example in your screenshot, if we were looking for the number 9 in the “Number of Orders ….” column, it would return 2.

I’d suggest using the LARGE and SMALL functions instead.

For your sheet, since you’ve stated that Assets are repeated throughout the sheet and you want to rank by category, I’d recommend first creating some helper columns. Here’s what I’d do:

Add a column to SUM the number of orders for each asset when it has a category of social, using the formula: =SUMIFS([Number of Orders by Sport with Duplicated Tags]:[Number of Orders by Sport with Duplicated Tags], Asset:Asset, Asset@row, [TV/SOCIAL]:[TV/SOCIAL], "social"). Convert this to a column formula. This will show us the sum for each row - so sums will be repeated each time the asset appears. We’ll address this in step 4!
Add an Auto Number column (I’ve called mine “Row ID”) and save the sheet to populate it.
Create another column (I’ve called mine “duplicate?”) that tells us if a row is the First Entry or a Duplicate. Use =IF(COUNTIFS(Asset:Asset, Asset@row, [TV/SOCIAL]:[TV/SOCIAL], [TV/SOCIAL]@row) > 1, IF(MIN(COLLECT([Row ID]:[Row ID], Asset:Asset, Asset@row)) = [Row ID]@row, "First Entry", "Duplicate")). This checks if the Asset and the category (TV/SOCIAL) is the same as the current row.
Then, in a new column, (I’ve called mine “formula to rank top number of orders (social)”), use the following formula: =LARGE(COLLECT([Formula to sum orders for each asset (social)]:[Formula to sum orders for each asset (social)], [duplicate?]:[duplicate?], "First Entry"), 1). Drag this down to the 4 cells below, and change the 1 to a 2 for the 2nd cell, 3 for the 3rd cell, and so on - the number determines the rank you want to return - so 1 is the highest number, 2 is the 2nd highest number, and so on.
In another column, you can use the SMALL function to return the lowest ordered numbers in the same way, so your first formula would be: =SMALL(COLLECT([Formula to sum orders for each asset (social)]:[Formula to sum orders for each asset (social)], [duplicate?]:[duplicate?], "First Entry"), 1).
Then, you can use an INDEX(MATCH) formula referencing the ranked numbers to return the asset names - I’d do this in two more columns, eg “Most ordered assets” and “Least ordered assets” and use the following formulas - these can either be dragged down to the next 4 cells or converted to column formulas.
1. =INDEX(Asset:Asset, MATCH([formula to rank top number of orders (social)]@row, [Formula to sum orders for each asset (social)]:[Formula to sum orders for each asset (social)], 0))
2. =INDEX(Asset:Asset, MATCH([formulas to rank least orders (social)]@row, [Formula to sum orders for each asset (social)]:[Formula to sum orders for each asset (social)], 0))

As an example of what this might look like, here’s my test sheet:

You can of course hide the columns you don’t need to see once you’ve got everything working correctly, and you can repeat the steps for the TV category if required.

I hope that’s at least a good starting point for you!

Georgie

All comments

Georgie

Hi @Mariana Fortuna,

I’d suggest using the LARGE and SMALL functions instead.

For your sheet, since you’ve stated that Assets are repeated throughout the sheet and you want to rank by category, I’d recommend first creating some helper columns. Here’s what I’d do:

Add a column to SUM the number of orders for each asset when it has a category of social, using the formula: =SUMIFS([Number of Orders by Sport with Duplicated Tags]:[Number of Orders by Sport with Duplicated Tags], Asset:Asset, Asset@row, [TV/SOCIAL]:[TV/SOCIAL], "social"). Convert this to a column formula. This will show us the sum for each row - so sums will be repeated each time the asset appears. We’ll address this in step 4!
Add an Auto Number column (I’ve called mine “Row ID”) and save the sheet to populate it.
Create another column (I’ve called mine “duplicate?”) that tells us if a row is the First Entry or a Duplicate. Use =IF(COUNTIFS(Asset:Asset, Asset@row, [TV/SOCIAL]:[TV/SOCIAL], [TV/SOCIAL]@row) > 1, IF(MIN(COLLECT([Row ID]:[Row ID], Asset:Asset, Asset@row)) = [Row ID]@row, "First Entry", "Duplicate")). This checks if the Asset and the category (TV/SOCIAL) is the same as the current row.
Then, in a new column, (I’ve called mine “formula to rank top number of orders (social)”), use the following formula: =LARGE(COLLECT([Formula to sum orders for each asset (social)]:[Formula to sum orders for each asset (social)], [duplicate?]:[duplicate?], "First Entry"), 1). Drag this down to the 4 cells below, and change the 1 to a 2 for the 2nd cell, 3 for the 3rd cell, and so on - the number determines the rank you want to return - so 1 is the highest number, 2 is the 2nd highest number, and so on.
In another column, you can use the SMALL function to return the lowest ordered numbers in the same way, so your first formula would be: =SMALL(COLLECT([Formula to sum orders for each asset (social)]:[Formula to sum orders for each asset (social)], [duplicate?]:[duplicate?], "First Entry"), 1).
Then, you can use an INDEX(MATCH) formula referencing the ranked numbers to return the asset names - I’d do this in two more columns, eg “Most ordered assets” and “Least ordered assets” and use the following formulas - these can either be dragged down to the next 4 cells or converted to column formulas.
1. =INDEX(Asset:Asset, MATCH([formula to rank top number of orders (social)]@row, [Formula to sum orders for each asset (social)]:[Formula to sum orders for each asset (social)], 0))
2. =INDEX(Asset:Asset, MATCH([formulas to rank least orders (social)]@row, [Formula to sum orders for each asset (social)]:[Formula to sum orders for each asset (social)], 0))

As an example of what this might look like, here’s my test sheet:

You can of course hide the columns you don’t need to see once you’ve got everything working correctly, and you can repeat the steps for the TV category if required.

I hope that’s at least a good starting point for you!

Georgie

Mariana Fortuna

Hi Georgie,

Thank you so much for your help! I have tested a few and it seems to be working!

Best,

Mariana

Georgie

Hi @Mariana Fortuna,

Glad I was able to help!

Georgie

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.