Need help with RANK function

[Art Dameron]

This works:

CONCATENATE(RC[2],"Rating")

But this does not work:

RANK(Rate,CONCATENATE(RC[2],"Rating"))

Is it not working because of an error in syntax or is it that you cannot use
an embedded statement like that inside the RANK formula?

Thanks for any help, Art.

 

[Pete_UK]

RANK only works on numbers - by using the CONCATENATE function you
will end up with text values.

Hope this helps.

Pete

 

[Art Dameron]

Thank you. Would there be a way for me to refer to another cell within the
RANK formula? Let me explain by example.

Here's the setup. This spreadsheet track football data. The main worksheet
has 225 rows of data (1 row for each team). One column is named Rate and one
column of data identifies the conference associated with that team. On a
separate worksheet all the teams are separated into conferences. Within that
section of data one of the columns is named BigTenRating, or BigEastRating,
etc.

Okay, back to the main worksheet. I'm trying to get a column of data that
will have a formula of RANK(Rate,BigTenRating). That works perfectly
(because Rate and BigTenRating are named) but I'm lazy and don't want to
type this 225 times (each time having to change the conference name). So, I
was trying to get the second part of the RANK formula to refer to the cell
that already holds the conference name. My first attempt was
RANK(Rate,RC[2]Rating) but that didn't work.

Can you think of a way to get that conference name inserted?

Thanks.



RANK only works on numbers - by using the CONCATENATE function you
will end up with text values.

Hope this helps.

Pete

 

[Pete_UK]

You would normally use INDIRECT("string") to allow you to build up
cell references as a string and use them in a formula, something like:

INDIRECT("Sheet1!A1:A"&B2)

where B2 might contain 16, for example.

I don't fully understand your explanation of what you are trying to
do, but perhaps you can incorporate something like this within your
RANK formula.

Hope this helps.

Pete

Thank you. Would there be a way for me to refer to another cell within the
RANK formula? Let me explain by example.

Here's the setup. This spreadsheet track football data. The main worksheet
has 225 rows of data (1 row for each team). One column is named Rate and one
column of data identifies the conference associated with that team. On a
separate worksheet all the teams are separated into conferences. Within that
section of data one of the columns is named BigTenRating, or BigEastRating,
etc.

Okay, back to the main worksheet. I'm trying to get a column of data that
will have a formula of RANK(Rate,BigTenRating). That works perfectly
(because Rate and BigTenRating are named) but I'm lazy and don't want to
type this 225 times (each time having to change the conference name). So, I
was trying to get the second part of the RANK formula to refer to the cell
that already holds the conference name. My first attempt was
RANK(Rate,RC[2]Rating) but that didn't work.

Can you think of a way to get that conference name inserted?

Thanks.


RANK only works on numbers - by using the CONCATENATE function you
will end up with text values.

Hope this helps.

Pete

This works:

CONCATENATE(RC[2],"Rating")

But this does not work:

RANK(Rate,CONCATENATE(RC[2],"Rating"))

Is it not working because of an error in syntax or is it that you cannot
use
an embedded statement like that inside the RANK formula?

Thanks for any help, Art.- Hide quoted text -

- Show quoted text -