Rank and Round Together

[RunRabbit13]

Is it possible in the same formula to Rank and Round together? I have the following formula but it is returning #N/A. I assume this is because the whole reference is not rounded which leads me to believe this is not possible to do in one step. However, I thought someone may know of a workaround that I cannot think of.

RANK.EQ(ROUND(A1,2),$A$1:$A$17,0)

Thanks!

 

[Twollaston]

Is it possible in the same formula to Rank and Round together? I have the following formula but it is returning #N/A. I assume this is because the whole reference is not rounded which leads me to believe this is not possible to do in one step. However, I thought someone may know of a workaround that I cannot think of.

RANK.EQ(ROUND(A1,2),$A$1:$A$17,0)

Thanks!

I recieved NA for this as well, does the rounded value match a value in the range? because i believe it should be included in the range or else the formula can't rank them.
You could try this:

Excel 2010
	K	L
7	1.2	3
8	1.4	3
9	1.6	3
10	2	2
11	3	1
Sheet5

Cell Formulas
Range	Formula
L7	=SUMPRODUCT((ROUND(K7,2)<=ROUND(K7:K11,0))/COUNTIF($K$7:$K$11,$K$7:$K$11))

you would want your formula to probably say
=SUMPRODUCT((ROUND($A1,2)<=ROUND($A$1:$A17,2))/COUNTIF($A$1:$A17,$A$1:$A17))

NOTE: when using the sumproduct ranking formula if you have duplicate values it will not skip the rank
For Example:
normal formula results for ties 1, 2, 2, 2 ,5
sumproduct rank results for ties 1, 2, 2, 2, 3

 

[RunRabbit13]

This is great! I knew there had to be a way to accomplish it. Thank you for taking the time and the solution.

 

[Twollaston]

No problem, glad you like it. Happy to help you