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

" wrote:

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

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


Replace @mailinator.com with @tiscali.co.uk


wrote in message
oups.com...
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

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

On Sep 19, 2:30 pm, Mike H wrote:
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



" wrote:
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- Hide quoted text -

- Show quoted text -

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

On Sep 19, 11:54 am, Bernd P wrote:
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

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


Replace @mailinator.com with @tiscali.co.uk


wrote in message
oups.com...
On Sep 19, 11:54 am, Bernd P wrote:
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

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 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


Replace @mailinator.com with @tiscali.co.uk


"Bernd P" wrote in message
oups.com...
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

On Sep 20, 3:37 pm, "Sandy Mann" wrote:
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


Replace @mailinator.com with @tiscali.co.uk

"Bernd P" wrote in message

oups.com...



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- Hide quoted text -

- Show quoted text -

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...

wrote in message
oups.com...
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..)

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


Replace @mailinator.com with @tiscali.co.uk