Rank Function

[Craig4670]

Hi!

I am working with a large spread sheet that i need to rank a pricing column. I have the rank formula working, however if there are cells that are the same, the rank skips the number and adds 1.

Cell L11: $2.75 Rank 3
Cell L12: $2.90 Rank 4
Cell L13: $2.30 Rank 1
Cell L14: $2.30 Rank 1

Rank # 3 should be ranked #2

The formula I am using is:
=Rank(L11,$L$11:$L$14,1)

I'm not sure how to fix this. Any help would be appreciated.

Thanks!

 

[T. Valko]

Hi!

I am working with a large spread sheet that i need to rank a pricing column. I have the rank formula working, however if there are cells that are the same, the rank skips the number and adds 1.

Cell L11: $2.75 Rank 3
Cell L12: $2.90 Rank 4
Cell L13: $2.30 Rank 1
Cell L14: $2.30 Rank 1

Rank # 3 should be ranked #2

The formula I am using is:
=Rank(L11,$L$11:$L$14,1)

I'm not sure how to fix this. Any help would be appreciated.

Thanks!

Try this...

=RANK(L11,L$11:L$14,1)+COUNTIF(L$11:L11,L11)-1

 

[T. Valko]

Try this...

=RANK(L11,L$11:L$14,1)+COUNTIF(L$11:L11,L11)-1

Ooops!

I see you still want ties ranked the same.

Try this...

Book1
	L	M
11	$2.75	2
12	$2.90	3
13	$2.30	1
14	$2.30	1
Sheet1

=SUMPRODUCT(--(L11>L$11:L$14),1/COUNTIF(L$11:L$14,L$11:L$14))+1

 

[Craig4670]

Thank you very much. The 2nd formula was what I was looking for. Thanks again!

 

[T. Valko]

Thank you very much. The 2nd formula was what I was looking for. Thanks again!

You're welcome. Thanks for the feedback!