Rank Function: Refer to more than one range

Hi

I would like to enter a RANK functino that refers to more than one range
when performing the rank calculation - is this possible? e.g.

I have numbers in A1:A10 and in C5:C15.

Can I do a rank that ranks a value in A5, say, in terms of its order within
ranges A1:A10 and C5:C15 combined?

e.g. something like =rank(A5,{A1:A10,C5:C15})

^note the above formula doesn't work but I hope illustrates the problem I'm
trying to solve.

Regards

You could set up a contiguous range elsewhere in your sheet, eg
F1:F21, and in F1 you can have:

=A1

and copy this down to F10, and in F11 have:

=C5

and copy this down to F21.

Then your rank function can refer to the range F1:F21.

Hope this helps.

Pete

On Sep 23, 3:31*pm, Gary T wrote:
Hi

I would like to enter a RANK functino that refers to more than one range
when performing the rank calculation - is this possible? *e.g.

I have numbers in A1:A10 and in C5:C15.

Can I do a rank that ranks a value in A5, say, in terms of its order within
ranges A1:A10 and C5:C15 combined?

e.g. something like =rank(A5,{A1:A10,C5:C15})

^note the above formula doesn't work but I hope illustrates the problem I'm
trying to solve.

Regards

Thanks Pete

I would prefer not to do that, as the range the rank is referring to is
large and is therefore increasing the size of the spreadsheet enormously.

Is there a way of incorporating the 2 ranges into the Rank function itself
without duplicating the data?

Regards

Gary

"Pete_UK" wrote:

You could set up a contiguous range elsewhere in your sheet, eg
F1:F21, and in F1 you can have:

=A1

and copy this down to F10, and in F11 have:

=C5

and copy this down to F21.

Then your rank function can refer to the range F1:F21.

Hope this helps.

Pete

On Sep 23, 3:31 pm, Gary T wrote:
Hi

I would like to enter a RANK functino that refers to more than one range
when performing the rank calculation - is this possible? e.g.

I have numbers in A1:A10 and in C5:C15.

Can I do a rank that ranks a value in A5, say, in terms of its order within
ranges A1:A10 and C5:C15 combined?

e.g. something like =rank(A5,{A1:A10,C5:C15})

^note the above formula doesn't work but I hope illustrates the problem I'm
trying to solve.

Regards

I don't know - I've always done it (on the rare occasions when I've
needed to) in the way I suggested.

But, someone else might know a way ...

Pete

On Sep 23, 3:49*pm, Gary T wrote:
Thanks Pete

I would prefer not to do that, as the range the rank is referring to is
large and is therefore increasing the size of the spreadsheet enormously.

Is there a way of incorporating the 2 ranges into the Rank function itself
without duplicating the data?

Regards

Gary



"Pete_UK" wrote:
You could set up a contiguous range elsewhere in your sheet, eg
F1:F21, and in F1 you can have:

=A1

and copy this down to F10, and in F11 have:

=C5

and copy this down to F21.

Then your rank function can refer to the range F1:F21.

Hope this helps.

Pete

On Sep 23, 3:31 pm, Gary T wrote:
Hi

I would like to enter a RANK functino that refers to more than one range
when performing the rank calculation - is this possible? *e.g.

I have numbers in A1:A10 and in C5:C15.

Can I do a rank that ranks a value in A5, say, in terms of its order within
ranges A1:A10 and C5:C15 combined?

e.g. something like =rank(A5,{A1:A10,C5:C15})

^note the above formula doesn't work but I hope illustrates the problem I'm
trying to solve.

Regards- Hide quoted text -

- Show quoted text -

Try this:

=RANK(A5,(A1:A10,C5:C15))

--
Biff
Microsoft Excel MVP


"Pete_UK" wrote in message
...
I don't know - I've always done it (on the rare occasions when I've
needed to) in the way I suggested.

But, someone else might know a way ...

Pete

On Sep 23, 3:49 pm, Gary T wrote:
Thanks Pete

I would prefer not to do that, as the range the rank is referring to is
large and is therefore increasing the size of the spreadsheet enormously.

Is there a way of incorporating the 2 ranges into the Rank function itself
without duplicating the data?

Regards

Gary



"Pete_UK" wrote:
You could set up a contiguous range elsewhere in your sheet, eg
F1:F21, and in F1 you can have:

=A1

and copy this down to F10, and in F11 have:

=C5

and copy this down to F21.

Then your rank function can refer to the range F1:F21.

Hope this helps.

Pete

On Sep 23, 3:31 pm, Gary T wrote:
Hi

I would like to enter a RANK functino that refers to more than one
range
when performing the rank calculation - is this possible? e.g.

I have numbers in A1:A10 and in C5:C15.

Can I do a rank that ranks a value in A5, say, in terms of its order
within
ranges A1:A10 and C5:C15 combined?

e.g. something like =rank(A5,{A1:A10,C5:C15})

^note the above formula doesn't work but I hope illustrates the
problem I'm
trying to solve.

Regards- Hide quoted text -

- Show quoted text -