using a ranking formula

[dis622]

Hi again

Need help

Using this formula =RANK(Q2,$Q$2:$Q$9)+COUNTIF(O$1:O1,Q2) to rank the 8 result. It work all good. Except when there is more than one sale that are the same.

So for example if the 8 results are different I get ranking from 1 to 8 but if there is one or more of the same result, I will have two number the same.

Like maybe two number one. Here there a way to fix this?


Thank you for your help.

 

[jimrward]

perhaps there is a slight mistake in your formula

=RANK(Q2,$Q$2:$Q$9)+COUNTIF(O$1:O1,Q2)

try

=RANK(Q2,$Q$2:$Q$9)+COUNTIF(Q$1:Q1,Q2)

 

[jimrward]

oops

maybe even

=RANK(Q2,$Q$2:$Q$9)+COUNTIF(Q$2:Q2,Q2)

 

[Domenic]

Try...

=RANK(Q2,$Q$2:$Q$9)+COUNTIF($Q$2:Q2,Q2)-1

Hope this helps!

 

[dis622]

Yes you are right, there was a mistake, thank you for helping out.

 

[Domenic]

Note that -1 is needed at the end of the formula. Otherwise the formula will return incorrect results.