# Rank Function

#### Craig4670

##### Board Regular
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!

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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

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
LM
11\$2.752
12\$2.903
13\$2.301
14\$2.301
Sheet1

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

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

Thank you very much. The 2nd formula was what I was looking for. Thanks again!
You're welcome. Thanks for the feedback!

Replies
25
Views
392
Replies
2
Views
438
Replies
8
Views
217
Replies
4
Views
171
Replies
8
Views
122

1,203,502
Messages
6,055,769
Members
444,822
Latest member
Hombre

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back