0

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

dcorks
  • 13
  • 6

1 Answers1

1

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 ROUND function, round the values of your trunc(sum(...),1) results to however many decimal places you may need. OR
  • From the top menu choose Format> Number> More formats> Custom number formats and create your own

enter image description here

Following that, you will be able to visually spot the differences.
Additionally you can use the RANK function

enter image description here

marikamitsos
  • 10,264
  • 20
  • 26
  • Thanks. Adding `round()` to my data cleared the original problem I was having with ranking. – dcorks Mar 12 '20 at 10:09