Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking a moving list
I have an issue with ranking a dynamic range. I have a list of data that I
need to rank, but each week the length of the list changes. The regular rank formula would work, but I have another caveat, the last item in this random length list needs to be excluded from ranking. So one week I may have 20 items to rank while excluding item # 20, but the next week I may have 24 items to rank excluding item #24. I would rather not update the formula every week. I feel offset may work, but I am unfamiliar with that function. Can anyone shed some light on this issue? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking a moving list
With your list of data starting in cell A2, put a formula like this into B2:
=IF(COUNT(A2:A$100)1,RANK(A2,OFFSET($A$2,0,0,COUN T($A$2:$A$100)-1,1),TRUE),"") and copy down to B100. HTH, Bernie MS Excel MVP "WBTKbeezy" wrote in message ... I have an issue with ranking a dynamic range. I have a list of data that I need to rank, but each week the length of the list changes. The regular rank formula would work, but I have another caveat, the last item in this random length list needs to be excluded from ranking. So one week I may have 20 items to rank while excluding item # 20, but the next week I may have 24 items to rank excluding item #24. I would rather not update the formula every week. I feel offset may work, but I am unfamiliar with that function. Can anyone shed some light on this issue? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking a moving list
That does work, however with the TRUE part in there it was ranking backwards.
I removed it and it returns what I expected, so thanks. I wish I understood the offset function better. Thanks, "Bernie Deitrick" wrote: With your list of data starting in cell A2, put a formula like this into B2: =IF(COUNT(A2:A$100)1,RANK(A2,OFFSET($A$2,0,0,COUN T($A$2:$A$100)-1,1),TRUE),"") and copy down to B100. HTH, Bernie MS Excel MVP "WBTKbeezy" wrote in message ... I have an issue with ranking a dynamic range. I have a list of data that I need to rank, but each week the length of the list changes. The regular rank formula would work, but I have another caveat, the last item in this random length list needs to be excluded from ranking. So one week I may have 20 items to rank while excluding item # 20, but the next week I may have 24 items to rank excluding item #24. I would rather not update the formula every week. I feel offset may work, but I am unfamiliar with that function. Can anyone shed some light on this issue? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking a moving list
That does work, however with the TRUE part in there it was ranking backwards.
That's why I said "a formula like this" ;-) Offset simply returns a range offset from the reference cell by a number of rows, columns, with a number of rows, and columns in it. OFFSET($A$2,0,0,COUNT($A$2:$A$100)-1,1) means "give me a range that is offset from cell A2 by 0 rows and 0 columns (ie, a range that starts at cell A2), and has one less row in it as there are values in A2:A100, that is one column wide." The above example returns a range that starts in A2 and has all the cells in A2:A100 that are filled, except the last one... the tricky part is figuring out where to put $s to make sure that the formula copies correctly.... HTH, Bernie MS Excel MVP "WBTKbeezy" wrote in message ... I removed it and it returns what I expected, so thanks. I wish I understood the offset function better. Thanks, "Bernie Deitrick" wrote: With your list of data starting in cell A2, put a formula like this into B2: =IF(COUNT(A2:A$100)1,RANK(A2,OFFSET($A$2,0,0,COUN T($A$2:$A$100)-1,1),TRUE),"") and copy down to B100. HTH, Bernie MS Excel MVP "WBTKbeezy" wrote in message ... I have an issue with ranking a dynamic range. I have a list of data that I need to rank, but each week the length of the list changes. The regular rank formula would work, but I have another caveat, the last item in this random length list needs to be excluded from ranking. So one week I may have 20 items to rank while excluding item # 20, but the next week I may have 24 items to rank excluding item #24. I would rather not update the formula every week. I feel offset may work, but I am unfamiliar with that function. Can anyone shed some light on this issue? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranking a list | Excel Worksheet Functions | |||
Ranking a List which must be within GROUPS | Excel Discussion (Misc queries) | |||
How to pick one or more items out of a list, moving to new list, moving up or down. (form) | Excel Discussion (Misc queries) | |||
Ranking a list | Excel Worksheet Functions | |||
need function to sum top ranking items in list | Excel Worksheet Functions |