How to Rank item value ignoring empty or blank cells?

edited 11/26/21 in Formulas and Functions
11/26/21 Edited 11/26/21
Answered - Pending Review

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

  • Devin LeeDevin Lee ✭✭✭

    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?

Sign In or Register to comment.