In Google Sheets, why does rank() treat two identical values differently?
I have a sheet with four values that I believe are all equal (i.e. =A1=B1 returns TRUE for each pair). However, when I use rank() on a list with those values, they receive different ranks.
To my knowledge, I'm not doing anything strange, such as a workaround to avoid duplicates. (In this scenario, I want duplicate ranks.) The values I'm trying to rank() were the result of a trunc(sum(...),1), so there aren't any hidden decimals places that I'm not noticing.
I'm just using rank(A1,A1:B1) and arrayformula(rank(A1:B1,A1:B1). These two formulas return different results, even.
Why is rank() treating these numbers as different? Is there some kind of flag or extra property on the cells that's not normally visible that is making them different?
This situation is a little hard to explain without seeing the data, so I've recreated the situation in this sheet: https://docs.google.com/spreadsheets/d/1cL_15WnKgrxhJfT5lYYIg4sRAzaAzbpP7nH9hju1Rv4/edit?usp=sharing
It has to do with the Floating Point Error than with RANK.
In any case and since you are trying to see if " ...there aren't any hidden decimals places that's I'm not noticing" you could follow a different approach.
- Using the
ROUNDfunction, round the values of yourtrunc(sum(...),1)results to however many decimal places you may need. OR - From the top menu choose
Format>Number>More formats>Custom number formatsand create your own
Following that, you will be able to visually spot the differences.
Additionally you can use the RANK function
