Issue with Rank Formula

Having issues with a Rank Formula. Any help is much appreciated. The formula is below.

=RANK(E2,$E$2:$E$26,0)+COUNTIF($E$2:E2,E2)-1


Moderator comment: Crossposted https://www.ozgrid.com/forum/forum/h...h-rank-formula

What's the problem?
What are you expecting, and what are you getting?

Upon download I found what appears to be a floating point issue in the data.

Entering

Formula:

=MATCH(E2:E26,E2:E26,0)

in an unused area returns this array.

Formula:

{1;1;1;1;1;6;1;8;1;1;11;1;1;1;1;1;1;1;1;1;1;1;1;24;1}

Those numbers (8 and 11) correspond with the $1,875.00 figures. They should both be 8s.

Shadowing column E with rounded values in column F (ROUND(E2,2)) and then changing the formula in column C to reference F produced expected results.

What is strange is that restoring references in C back to column E now produced the expected returns, and

Formula:

=MATCH(E2:E26,E2:E26,0)

then returned the array

Formula:

{1;1;1;1;1;6;1;8;1;1;8;1;1;1;1;1;1;1;1;1;1;1;1;24;1}

. In other words the floating point seemed to disappear from column E and your formula worked fine!!

Please try the MATCH function at your end to confirm the differences between the $1,875.00 figures. RANK and COUNTIF seem to be evaluating them differently.

As for the evident "self correction" I have no explanations.

Definitely a floating point error. I put =(1875-E9) into F9, formatted as scientific, and got a result of -2.27E-13 -- not exactly 0 In F12, the results was 0.00E+0 (exactly 0). So the two 1875 values are not exactly the same, and Excel is seeing the value in E9 as slightly larger than then the value in E12.

I followed Dave's sequence (ROUND() function in column F, replace references in C to column F, then replace references to F back to E). When I changed the references to F, I got the expected results that Dave noted. However, when I reverted back to E references, the erroneous results returned.

The sample you have given has "constants" in column E, so we have no idea how these values are calculated. Obviously something in the process is susceptible to floating point error. You will need to do something with the calculation process to account for floating point error. A ROUND() function may be enough, but you will need to test that.

@ MrShorty

Thank you for attending to this and confirming FP. Those looked like hard numbers, but then again we've seen that before.

That leaves accounting for the missing rank 4. Tested independently COUNTIF apparently ignores the tiny differences due to floating point while RANK and RANK.EQ do not.

Floating point seems to be something Excel ... or computers are rather fickle about. LOL

The root issue does seem to be the difference noted above. If 1875 is entered in E5, the results give two 4s and no 5 - other similar tests (with same or other numbers) give similar problems. Unless there's a way of fixing this by putting ROUND into the formulae themselves (which I haven't found in the few minutes I've looked at this), then I think the easiest solution will be a helper column using ROUND, as MrShorty implied above.

Formula:

F2:  =ROUND(E2,2)

C2:  =RANK(F2,$F$2:$F$26,0)+COUNTIF($F$2:F2,F2)-1

I'm sure someone else might have a more elegant solution.

=RANK(E2,$E$2:$E$26,0)+0.001*COUNTIF($E$2:E2,E2)

Try this formula

Has everyone overcomplicated this?? It would be nice if you had told us what result were you expecting to see!!

Maybe:

1,2,3,4,5,5,5,5,5,5,etc
where the two 1875s are 3 and 4...

or

1,2,3,3,4,4,4,etc
where the are both given rank 3 and there are no missing values

or 1,2,3,3,5,5,5,etc

Here are all 3 options...