Unique Rank function not working

  • Thread starter Thread starter bpkaufman
  • Start date Start date
B

bpkaufman

Can anyone figure out why this formula would not be working (all cell
formatting is in tact):

RANK(G6,G$6:G$130)+COUNTIF(G$6:G6, G6)-1

Thanks in advance,

BK
 
Hi,

What do you want it to do?

The first part
=RANK(G6,G$6:G$130)
will rank G6 in the range G6 - G30 and works fine. If you drag it down it
will rank G7 etc.

The second part is confusing
+COUNTIF(G$6:G6, G6)-1
As displayed it wil count how often the value in G6 appears in the range
G6-G6 and subtract 1 so it will always evaluate as zero.
If you drag it down it becomes
+COUNTIF(G$6:G7, G7)-1
at this point it is evaluating a 2 cell range for whatever is in G7
What do you want it to do?

Mike
 
What does "would not be working" mean? Nothing returned? Error value? or
what?

Is G$6:G$130 sorted in assending order?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Mike, the COUNTIF bit at the end will give rise to unique ranks where
duplicates occur in the range - instead of getting, say, three ranks
of 5 and then the next rank of 8 if there are three equal values, this
will add 0 for the first occurrence, 1 for the second occurrence and 2
for the 3rd occurrence, turning the ranks into 5, 6 and 7
respectively.

Pete
 
Hello,

Are you using text values?

Anyway, I would always use
=COUNTIF(G$6:G$130,">"&G6)+COUNTIF(G$6:G6,G6)
and copy down.

Works both for numbers and for text.

Regards,
Bernd
 
Hello,

Are you using text values?

Anyway, I would always use
=COUNTIF(G$6:G$130,">"&G6)+COUNTIF(G$6:G6,G6)
and copy down.

Works both for numbers and for text.

Regards,
Bernd
Click to expand...

Thanks for the responses...Just trying to get unique ranks (i.e. no
ties) for these numbers. The formula in my intitial post is supposed
to do the trick but I am still getting ties...
 
Hello again,

There might be *a* text value in your data.

Does it work with my approach?

Regards,
Bernd
 
Even with G6:G130 having all the same number I don't get any ties with your
formula. What sort of data do you have?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Hello Sandy,

Maybe I misunderstood you.

My formula ensures that there will be no ties at all. If values are
identical the first occurances gets the highest rank.

If you do want ties then omit the second countif.

Regards,
Bernd
 
Hi Bernd,

My comments were actually intended for the OP but looking at how I posted I
see how it was misleading. The OP posted:

RANK(G6,G$6:G$130)+COUNTIF(G$6:G6, G6)-1

and said in another post:

"Just trying to get unique ranks (i.e. no
ties) for these numbers. The formula in my intitial post is supposed
to do the trick but I am still getting ties..."

Thus I posted that I did not get any ties.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Hi Bernd,

My comments were actually intended for the OP but looking at how I posted I
see how it was misleading. The OP posted:

RANK(G6,G$6:G$130)+COUNTIF(G$6:G6, G6)-1

and said in another post:

"Just trying to get unique ranks (i.e. no
ties) for these numbers. The formula in my intitial post is supposed
to do the trick but I am still getting ties..."

Thus I posted that I did not get any ties.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk










- Show quoted text -
Click to expand...

Bernd seems to work.

Funny to me that the formula I provided intitally does not work (it
was provided by chip pearson)..any explanations? (I have the ranking
fornula in the column to the left of the data and the data is not in
any order..)

Thank you all very much for your help...
 
Funny to me that the formula I provided intitally does not work (it
was provided by chip pearson)..any explanations? (I have the ranking
fornula in the column to the left of the data and the data is not in
any order..)
Click to expand...

No I con't explain why your original formula did not work for you. The
RANK() funtion will return ties but the COUNTIF() function will add an
asscending number to it to prevent the same number being returned by the
formula. This will happen even if the *numbers* are actually text.

Anyway I'm glad that you got a formula that works for you.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top