If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Rank Function: Refer to more than one range
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 |
#3
|
|||
|
|||
Rank Function: Refer to more than one range
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 |
#4
|
|||
|
|||
Rank Function: Refer to more than one range
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 - |
#5
|
|||
|
|||
Rank Function: Refer to more than one range
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 - |
Thread Tools | |
Display Modes | |
|
|