How to Rank item value ignoring empty or blank cells?

Hello. need help on this. I'm trying to rank the item name based on value % (highest to lowest). When one item name has no value % it gives a #NO MATCH on that item row and on the succeeding rows/item. Please see screenshot (sample).
Thank you. 😊
Answers
-
Hey @Elena Misa,
There just needs to be an additional condition to the COUNTIF statement checking for a blank cell. Use try formula below.
=IF([Value%]@row = "", "", RANKEQ([Value%]@row, [Value%]:[Value%]) + COUNTIFS([Value%]$1:[Value%]@row, [Value%]@row <> "", [Value%]$1:[Value%]@row, RANKEQ(@cell, [Value%]:[Value%]) = RANKEQ([Value%]@row, [Value%]:[Value%])) - 1)
-
Hi @Devin Lee,
Thank you for your reply. Using your formula it gives an #INVALID DATA if cell has a value (value % column). Looking for formula that ranking of values will be continous even if there's a blank/empty cell(s).
-
Hi @Elena Misa
I've tested Devin's formula and it works fine on my sheet, however you could be getting an INVALID DATA TYPE error if somehow the % column is seen as text instead of numerical values.
How did you enter in the Value %? Is it possible that these numbers are being read as text?
Need more information? 👀 | 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
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!