Ranking Formula

[Guest]

What is the formula to use that when ranking and two numbers are the same it
will just assign it the next numerical value. I am
using"=RANK($C49,$C$49:$C$62,1)" some cells automatically will go to the next
number, however most will not. I am throroughly confused!!!

 

[Biff]

Hi!

That's how RANK works.

Try this:

=RANK($C49,$C$49:$C$62,1)+COUNTIF($C$49:$C49,$C49)-1

The first instance of a tie will be ranked higher:

25 = 4
10 = 1
10 = 2
22 = 3

Biff

 

[Guest]

That doesn't quite work. I have a list of 14 production numbers that I want
to rank, with this ranking it will then autofill into a inventory spreadsheet
that has them sorted using the choose function. The weird thing about it is
the first cell will automatically go to the next number in instance of a tie,
the rest will stay the same number. All formatting is exactly the same. Is
there another way I could get these numbers to sort?? Thank you for your
help!

 

[Biff]

I have a list of 14 production numbers that I want to rank

The formula I suggested does just that!

Your problem is probably here:

with this ranking it will then autofill into a inventory spreadsheet
that has them sorted using the choose function. The weird thing about it
is
the first cell will automatically go to the next number in instance of a
tie,
the rest will stay the same number. All formatting is exactly the same.
Is
there another way I could get these numbers to sort??

But you didn't explain any of this in your original post. What does your
Choose formula do? Post that formula.

Biff

 

[Guest]

Choose Formula
=CHOOSE($H49,C$49,C$50,C$51,C$52,C$53,C$54,C$55,C$56,C$57,C$58,C$59,C$60,C$61,C$62) h49 is match formula
Match Formula =MATCH(G49,$D$4962,0) (G49 is rank)
It works perfect if there is not any ties. the only problem is if there is,
for instance, two 6th place, then there is no 7. The match formula for 7
then comes back as
#N/A

 

[Biff]

Ok, so what you need is a rank formula that breaks any ties. That's exactly
what the formula I suggested does.

Replace your current rank formula:

=RANK($C49,$C$49:$C$62,1)

With this rank formula:

=RANK($C49,$C$49:$C$62,1)+COUNTIF($C$49:$C49,$C49)-1

Now, your Choose formula should work properly.

You can use this in place of the Choose formula and eliminate the Match
formula altogether: (unless it's used elsewhere)

=INDEX(C$49:C$62,MATCH(G49,D$49$62,0))

Biff