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?
Help Article Resources
Categories
Check out the Formula Handbook template!