software

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
usingquot;=RANK($C49,$C$49:$C$62,1)quot; some cells automatically will go to the next
number, however most will not. I am throroughly confused!!!

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

quot;Todd Nelsonquot; gt; wrote in message
...
gt; What is the formula to use that when ranking and two numbers are the same
gt; it
gt; will just assign it the next numerical value. I am
gt; usingquot;=RANK($C49,$C$49:$C$62,1)quot; some cells automatically will go to the
gt; next
gt; number, however most will not. I am throroughly confused!!!
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!

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; That's how RANK works.
gt;
gt; Try this:
gt;
gt; =RANK($C49,$C$49:$C$62,1) COUNTIF($C$49:$C49,$C49)-1
gt;
gt; The first instance of a tie will be ranked higher:
gt;
gt; 25 = 4
gt; 10 = 1
gt; 10 = 2
gt; 22 = 3
gt;
gt; Biff
gt;
gt; quot;Todd Nelsonquot; gt; wrote in message
gt; ...
gt; gt; What is the formula to use that when ranking and two numbers are the same
gt; gt; it
gt; gt; will just assign it the next numerical value. I am
gt; gt; usingquot;=RANK($C49,$C$49:$C$62,1)quot; some cells automatically will go to the
gt; gt; next
gt; gt; number, however most will not. I am throroughly confused!!!
gt;
gt;
gt;

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

The formula I suggested does just that!

Your problem is probably he

gt;with this ranking it will then autofill into a inventory spreadsheet
gt; that has them sorted using the choose function. The weird thing about it
gt; is
gt; the first cell will automatically go to the next number in instance of a
gt; tie,
gt; the rest will stay the same number. All formatting is exactly the same.
gt; Is
gt; 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

quot;Todd Nelsonquot; gt; wrote in message
...
gt; That doesn't quite work. I have a list of 14 production numbers that I
gt; want
gt; to rank, with this ranking it will then autofill into a inventory
gt; spreadsheet
gt; that has them sorted using the choose function. The weird thing about it
gt; is
gt; the first cell will automatically go to the next number in instance of a
gt; tie,
gt; the rest will stay the same number. All formatting is exactly the same.
gt; Is
gt; there another way I could get these numbers to sort?? Thank you for your
gt; help!
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Hi!
gt;gt;
gt;gt; That's how RANK works.
gt;gt;
gt;gt; Try this:
gt;gt;
gt;gt; =RANK($C49,$C$49:$C$62,1) COUNTIF($C$49:$C49,$C49)-1
gt;gt;
gt;gt; The first instance of a tie will be ranked higher:
gt;gt;
gt;gt; 25 = 4
gt;gt; 10 = 1
gt;gt; 10 = 2
gt;gt; 22 = 3
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;Todd Nelsonquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; What is the formula to use that when ranking and two numbers are the
gt;gt; gt; same
gt;gt; gt; it
gt;gt; gt; will just assign it the next numerical value. I am
gt;gt; gt; usingquot;=RANK($C49,$C$49:$C$62,1)quot; some cells automatically will go to
gt;gt; gt; the
gt;gt; gt; next
gt;gt; gt; number, however most will not. I am throroughly confused!!!
gt;gt;
gt;gt;
gt;gt;
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

quot;Biffquot; wrote:

gt; gt;I have a list of 14 production numbers that I want to rank
gt;
gt; The formula I suggested does just that!
gt;
gt; Your problem is probably he
gt;
gt; gt;with this ranking it will then autofill into a inventory spreadsheet
gt; gt; that has them sorted using the choose function. The weird thing about it
gt; gt; is
gt; gt; the first cell will automatically go to the next number in instance of a
gt; gt; tie,
gt; gt; the rest will stay the same number. All formatting is exactly the same.
gt; gt; Is
gt; gt; there another way I could get these numbers to sort??
gt;
gt; But you didn't explain any of this in your original post. What does your
gt; Choose formula do? Post that formula.
gt;
gt; Biff
gt;
gt; quot;Todd Nelsonquot; gt; wrote in message
gt; ...
gt; gt; That doesn't quite work. I have a list of 14 production numbers that I
gt; gt; want
gt; gt; to rank, with this ranking it will then autofill into a inventory
gt; gt; spreadsheet
gt; gt; that has them sorted using the choose function. The weird thing about it
gt; gt; is
gt; gt; the first cell will automatically go to the next number in instance of a
gt; gt; tie,
gt; gt; the rest will stay the same number. All formatting is exactly the same.
gt; gt; Is
gt; gt; there another way I could get these numbers to sort?? Thank you for your
gt; gt; help!
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt;gt; Hi!
gt; gt;gt;
gt; gt;gt; That's how RANK works.
gt; gt;gt;
gt; gt;gt; Try this:
gt; gt;gt;
gt; gt;gt; =RANK($C49,$C$49:$C$62,1) COUNTIF($C$49:$C49,$C49)-1
gt; gt;gt;
gt; gt;gt; The first instance of a tie will be ranked higher:
gt; gt;gt;
gt; gt;gt; 25 = 4
gt; gt;gt; 10 = 1
gt; gt;gt; 10 = 2
gt; gt;gt; 22 = 3
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt; quot;Todd Nelsonquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; What is the formula to use that when ranking and two numbers are the
gt; gt;gt; gt; same
gt; gt;gt; gt; it
gt; gt;gt; gt; will just assign it the next numerical value. I am
gt; gt;gt; gt; usingquot;=RANK($C49,$C$49:$C$62,1)quot; some cells automatically will go to
gt; gt;gt; gt; the
gt; gt;gt; gt; next
gt; gt;gt; gt; number, however most will not. I am throroughly confused!!!
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

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

quot;Todd Nelsonquot; gt; wrote in message
...
gt; Choose Formula
gt; =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)
gt; h49 is match formula
gt; Match Formula =MATCH(G49,$D$4962,0) (G49 is rank)
gt; It works perfect if there is not any ties. the only problem is if there
gt; is,
gt; for instance, two 6th place, then there is no 7. The match formula for 7
gt; then comes back as
gt; #N/A
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; gt;I have a list of 14 production numbers that I want to rank
gt;gt;
gt;gt; The formula I suggested does just that!
gt;gt;
gt;gt; Your problem is probably he
gt;gt;
gt;gt; gt;with this ranking it will then autofill into a inventory spreadsheet
gt;gt; gt; that has them sorted using the choose function. The weird thing about
gt;gt; gt; it
gt;gt; gt; is
gt;gt; gt; the first cell will automatically go to the next number in instance of
gt;gt; gt; a
gt;gt; gt; tie,
gt;gt; gt; the rest will stay the same number. All formatting is exactly the
gt;gt; gt; same.
gt;gt; gt; Is
gt;gt; gt; there another way I could get these numbers to sort??
gt;gt;
gt;gt; But you didn't explain any of this in your original post. What does your
gt;gt; Choose formula do? Post that formula.
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;Todd Nelsonquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; That doesn't quite work. I have a list of 14 production numbers that I
gt;gt; gt; want
gt;gt; gt; to rank, with this ranking it will then autofill into a inventory
gt;gt; gt; spreadsheet
gt;gt; gt; that has them sorted using the choose function. The weird thing about
gt;gt; gt; it
gt;gt; gt; is
gt;gt; gt; the first cell will automatically go to the next number in instance of
gt;gt; gt; a
gt;gt; gt; tie,
gt;gt; gt; the rest will stay the same number. All formatting is exactly the
gt;gt; gt; same.
gt;gt; gt; Is
gt;gt; gt; there another way I could get these numbers to sort?? Thank you for
gt;gt; gt; your
gt;gt; gt; help!
gt;gt; gt;
gt;gt; gt; quot;Biffquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Hi!
gt;gt; gt;gt;
gt;gt; gt;gt; That's how RANK works.
gt;gt; gt;gt;
gt;gt; gt;gt; Try this:
gt;gt; gt;gt;
gt;gt; gt;gt; =RANK($C49,$C$49:$C$62,1) COUNTIF($C$49:$C49,$C49)-1
gt;gt; gt;gt;
gt;gt; gt;gt; The first instance of a tie will be ranked higher:
gt;gt; gt;gt;
gt;gt; gt;gt; 25 = 4
gt;gt; gt;gt; 10 = 1
gt;gt; gt;gt; 10 = 2
gt;gt; gt;gt; 22 = 3
gt;gt; gt;gt;
gt;gt; gt;gt; Biff
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Todd Nelsonquot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt; What is the formula to use that when ranking and two numbers are the
gt;gt; gt;gt; gt; same
gt;gt; gt;gt; gt; it
gt;gt; gt;gt; gt; will just assign it the next numerical value. I am
gt;gt; gt;gt; gt; usingquot;=RANK($C49,$C$49:$C$62,1)quot; some cells automatically will go to
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt; next
gt;gt; gt;gt; gt; number, however most will not. I am throroughly confused!!!
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;