RANKEQ showing multiple 1 Ranks but still showing a rank of 2
I'm running into an issue using RANKEQ to determine the top 10 categories with some criteria while working with census data. The formula I'm using is:
=IFERROR(RANKEQ([Speak English less than "Very Well"]@row, COLLECT([Speak English less than "Very Well"]:[Speak English less than "Very Well"], County:County, =County@row, Hierarchy:Hierarchy, Hierarchy@row = 0)), "")
This works for most of my data set, however, for two Counties there are multiple ranks of 1 along with a rank of 2 which I cannot determine a cause for (see example)
I'm not sure why the hierarchy being set a "1" for Spanish and Spanish Creole line is getting counted as 1 along with Spanish with the hierarchy set a zero.
Any insight the community would have would be much appreciated!
Best Answer
-
Hi @OGSMike
Do you have "Spanish and Spanish Creole" anywhere else in the sheet?
The Collect function isn't looking at this specific row for the 0 Hierarchy. It's looking to see if this "speak english" value appears anywhere in the sheet with this row's county and with 0, then ranking this row against those rows.
I would suggest adding an IF statement at the front, like so:
=IFERROR(IF(Hierarchy@row <> 0, "", RANKEQ([Speak English less than "Very Well"]@row, COLLECT([Speak English less than "Very Well"]:[Speak English less than "Very Well"], County:County, County@row, Hierarchy:Hierarchy, 0))), "")
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi @OGSMike
Do you have "Spanish and Spanish Creole" anywhere else in the sheet?
The Collect function isn't looking at this specific row for the 0 Hierarchy. It's looking to see if this "speak english" value appears anywhere in the sheet with this row's county and with 0, then ranking this row against those rows.
I would suggest adding an IF statement at the front, like so:
=IFERROR(IF(Hierarchy@row <> 0, "", RANKEQ([Speak English less than "Very Well"]@row, COLLECT([Speak English less than "Very Well"]:[Speak English less than "Very Well"], County:County, County@row, Hierarchy:Hierarchy, 0))), "")
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you @Genevieve P. that worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!