Sumproduct for Ranking
I have a formula in excel which evaluates a range of cells then provides a ranking without skipping numbers (1, 2, 3...etc.) Additionally, duplicate values are assigned the same rank, I need this.
I've tried playing around with the RANKEQ formula but some rank numbers are being skipped.
Excel Formula:
=SUMPRODUCT(($R$5:$R$1615<$R5)/COUNTIF($R$5:$R$1615,$R$5:$R$1615))+1
Here's the formula I'm using which works, except for skipping rank numbers...
Smartsheet Formula:
=RANKEQ([AMYR]@row, [AMYR]:[AMYR]) + COUNTIF([AMYR]@row:[AMYR]@row, RANKEQ(@cell, [AMYR]:[AMYR]) = RANKEQ([AMYR]@row, [AMYR]:[AMYR])) - 1
Here's an example of what I'm seeing, sorted highest value:
Any suggestions would be greatly appreciated. Thanks!
Answers
-
Hi @Eric G.
Here's another Community thread that discusses Ranking without Duplicating Ranks.
One of the solutions looks very similar to yours, with a couple of tweaks.
The COUNTIF at the end of your formula should be COUNTIFS with an S, and you will need to look for if [AMYR]:[AMYR] is not blank (versus your range of [AMYR]@row:[AMYR]@row).
You would also need to add the system-generated auto-number column [Row ID] as a helper column. I've copied over the formula from that post:
=RANKEQ(A@row, A:A) + COUNTIFS(A:A, ISNUMBER(@cell), [Row ID]:[Row ID], <=[Row ID]@row, A:A, RANKEQ(@cell, A:A) = RANKEQ(A@row, A:A)) - 1)
Here's how to adjust your formula to match this structure:
=RANKEQ([AMYR]@row, [AMYR]:[AMYR]) + COUNTIFS([AMYR]:[AMYR], <> "", [Row ID]:[Row ID], <=[Row ID]@row, RANKEQ(@cell, [AMYR]:[AMYR]) = RANKEQ([AMYR]@row, [AMYR]:[AMYR])) - 1
Let me know if this works!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Genevieve P.!
Sorry for the delay, I put this project on the back burner but now picking back up where I left off.
Thank you for the suggestion, I tried modifying the updated formula however, now I'm getting a #CIRCULAR REFERENCE error. I'm trying to sort it out, but maybe it has to do with changing from row to column?
"and you will need to look for if [AMYR]:[AMYR] is not blank (versus your range of [AMYR]@row:[AMYR]@row)."
Thanks!
-
FYI @Genevieve P., I just re-entered the formula and now I'm getting an #INVALID OPERATION error. FYI, I have some values that are "0" if that matters.
-
Hi @Eric G
My apologies, I was missing a Range reference in the formula above!
=RANKEQ(AMYR@row, AMYR:AMYR) + COUNTIFS(AMYR:AMYR, <>"", [Row ID]:[Row ID], <=[Row ID]@row, AMYR:AMYR, RANKEQ(@cell, AMYR:AMYR) = RANKEQ(AMYR@row, AMYR:AMYR)) - 1
This should be input in its own column, and then can be converted to a Column Formula.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Genevieve P., I used the updated formula and it worked. However, I'm back where I was in skipping some of the rankings. Here are the latest results I pasted into excel...
Numbers 3, 6, and 7 were skipped.
-
Hi @Eric G
Would you be able to post a screen capture with the formula "open" in Smartsheet, like I have above? Showing the AMYR column and your RowID column, but blocking out any other sensitive data.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P., yes of course! FYI, AMYR = Average MY x Ranking
-
Aha! Thank you, this helps!
It looks like you're missing the third system auto-number helper column:
- Column to Rank (Average MY x Ranking)
- Auto-Number column (called...?)
- Column with Formula (Average MY & Weight Ranking)
In my formula I'm looking over at an auto-number column in the sheet, called "Row ID". Notice that the formula is placed in a separate column.
=RANKEQ(AMYR@row, AMYR:AMYR) + COUNTIFS(AMYR:AMYR, <>"", [Row ID]:[Row ID], <=[Row ID]@row, AMYR:AMYR, RANKEQ(@cell, AMYR:AMYR) = RANKEQ(AMYR@row, AMYR:AMYR)) - 1
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hey @Genevieve P., I realized the formula being shown was the original one, and not the updated one. I entered the updated one as so...
=RANKEQ([Average MY x Ranking]@row, [Average MY x Ranking]:[Average MY x Ranking]) + COUNTIFS([Average MY x Ranking]:[Average MY x Ranking], <>"", [Row ID]:[Row ID], <=[Row ID]@row, [Average MY x Ranking]:[Average MY x Ranking], RANKEQ(@cell, [Average MY x Ranking]:[Average MY x Ranking]) = RANKEQ([Average MY x Ranking]@row, [Average MY x Ranking]:[Average MY x Ranking])) - 1
Good news is I got the result I wanted!
Bad news is when I try saving the sheet I get the following error...
😥
-
Hi @Eric G
I'm glad we got the formula working! 🙂
In regards to that error, have you been able to save the sheet? I would personally first go through a few troubleshooting steps (check network connection, try a different browser, save other sheets, etc). If that hasn't resolved the error, please do contact Support as the pop-up window suggests, with the sheet URL and a screen recording of the issue, including the steps you took leading up to this.
Thanks!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!