RANK Function error - "RANK has no valid input data"

**KristinA217** · 10-18-2021, 02:38 PM

I am on the verge of tearing all of my hair out, please help!

Here is what I need: In cell M2, I need it to tell me the rank of cell L2 in comparison to cells L10 and L18.
Here is what I use: =RANK(L2,$L$10,$L$18)
Here is what I get: N/A - "RANK has no valid input data"

The closest I can get to isolating the problem is the fact that the values in L2, L10, and L18 all have two decimal places - the function seems to work for cells whose value has no decimal places... how do I get it to accept and calculate for the two decimal place values?

Yes, I have formatted all cells to be numbers, not text.

Link to Google Sheet in attachment.

TIA!

**MrShorty** · 10-18-2021, 03:48 PM

I am unable to follow your link (probably a problem on my end, and not yours). I am unable to create a scenario from scratch (number of decimal places doesn't matter) where any RANK() function with 3 arguments like this works. What I see Google doing with this function:

1) Take the value in L2.
2) Look for that value in the range L10. Because L10 is a single cell, there is only one value to compare to, so the test essentially boils down to "is the value in L2 the same as L10".
3) If L2 is the same as L10, then return 1 (they are the same value, so the value is the highest/lowest rank). Otherwise, return N/A (since the value does not exist in the range L10).

One usually expects that the second argument in the RANK() function will be a multi-cell range rather than a single cell. Something like =RANK(L2,$L$2:$L$18). You're using 3 single cell inputs, which suggests to me that you are either misunderstanding how the RANK() function works, or you are trying to do something unexpected with this.

As far as I know, RANK() behaves the same in both Sheets and Excel. You could try downloading your Sheets file as an xlsx document, then upload the spreadsheet directly to the forum (that would avoid problems with linking directly to google sheets).

**KristinA217** · 10-18-2021, 05:13 PM

Thank you for taking a look at this!

I replicated everything in Excel, and I get the same error. What is a mystery to me is that the function works as expected only when the values in L2, L10, and L18 have no decimal places.

It seems like RANK is the correct function to use for what I want it to do (return a rank for non-adjacent cells), but maybe I am missing a better function for this?