Eliminating duplicate values when using RANK

[sosulli80]

Hoping someone can help. Have tried several different methods to reach the desired effect without any luck.

Looking to have the cities tied in 9th place instead appear as 9th, 10th, & 11th.

Doesn't matter to me in what order they are ranked, but can use "target" in descending order if criteria is needed

Have attempted several iferror and countif examples I've found on forums, but none seem to work

Who can be my hero?

City actual target % to target Rank
Houston 0 6.83 0.00% 9
London 2 6.25 32.00% 6
San Antonio 0 4.23 0.00% 9
New York 4 6.30 63.52% 3
Los Angeles 8 12.55 63.72% 2
New Delhi 1 4.42 22.63% 8
Mumbai 2 6.05 33.06% 5
Queensland 2 7.33 27.30% 7
Paris 3 3.10 96.71% 1
Tokyo 1 2.72 36.70% 4
Hong Kong 9 0.00 0.00% 9
Chicago 23 59.78 38.47%

[AliGW]

See if this works for you:

=SUMPRODUCT(N(($H$6:$H$17-ROW($H$6:$H$17)/10000)>=(H6-ROW(H6)/10000)))

If that doesn't do it, have a look here: http://www.tushar-mehta.com/excel/ne...ing/index.html

[Khalidngo]

Hi,

Try this and copy down:
=RANK(H6,$H$6:$H$17,0)+COUNTIF($H$6:H6,H6)-1

Regular formula, Don't need to array entered.

Blessing

[sosulli80]

Worked like a charm! Thanks for your help!

[sosulli80]

Hi there,

Had tried this and it didn't work. Thankfully AliGW solved for me

[AliGW]

You're welcome!