| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding last column in non-contiguous range
I have two ranges that contain placeholders for project labor (hours)
estimates: C10:N10 (representing Jan - Dec 2007) C20:N20 (representing Jan - Dec 2008) Please note that a project can start and end in any month (i.e., it does not necessarily have to start in Jan or end in Dec.) The following formula calculates the month containing the last estimate (but only in the first range): =COLUMN(OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N10, 1)-1))-2 However, although all my project start sometime in 2007, many do not end until sometime in 2008. Consequently, I need to somehow incorporate the second range into the aforementioned formula. Unfortunately, the MATCH function does not permit non-contiguous ranges. Can anyone tell me how to re-write my formula so that it can include both ranges? Also, if a given project's last estimate occurred in April 2008, for example, I would like the result to return 16 (rather than 4), assuming Jan 2007 = 1. Thanks in advance for any help. Bob |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding last column in non-contiguous range
Sorry, I forgot to also mention that the formula below does not work when
C10:N10 contains a formula. So I would appreciate any help in modifying the formula to find the last column containing a non-zero value. Thanks again. "Bob" wrote: I have two ranges that contain placeholders for project labor (hours) estimates: C10:N10 (representing Jan - Dec 2007) C20:N20 (representing Jan - Dec 2008) Please note that a project can start and end in any month (i.e., it does not necessarily have to start in Jan or end in Dec.) The following formula calculates the month containing the last estimate (but only in the first range): =COLUMN(OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N10, 1)-1))-2 However, although all my project start sometime in 2007, many do not end until sometime in 2008. Consequently, I need to somehow incorporate the second range into the aforementioned formula. Unfortunately, the MATCH function does not permit non-contiguous ranges. Can anyone tell me how to re-write my formula so that it can include both ranges? Also, if a given project's last estimate occurred in April 2008, for example, I would like the result to return 16 (rather than 4), assuming Jan 2007 = 1. Thanks in advance for any help. Bob |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding last column in non-contiguous range
Bob,
=IF(ISERROR(COLUMN(OFFSET(C20,0,MATCH(MAX(C20:N20) +1,C20:N20,1)-1))-2),COLUMN(OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N1 0,1)-1))-2,12 +COLUMN(OFFSET(C20,0,MATCH(MAX(C20:N20)+1,C20:N20, 1)-1))-2) will work if you modify the formulas to return "" and not 0 when they should not be counted in your project schedule. HTH, Bernie MS Excel MVP "Bob" wrote in message ... I have two ranges that contain placeholders for project labor (hours) estimates: C10:N10 (representing Jan - Dec 2007) C20:N20 (representing Jan - Dec 2008) Please note that a project can start and end in any month (i.e., it does not necessarily have to start in Jan or end in Dec.) The following formula calculates the month containing the last estimate (but only in the first range): =COLUMN(OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N10, 1)-1))-2 However, although all my project start sometime in 2007, many do not end until sometime in 2008. Consequently, I need to somehow incorporate the second range into the aforementioned formula. Unfortunately, the MATCH function does not permit non-contiguous ranges. Can anyone tell me how to re-write my formula so that it can include both ranges? Also, if a given project's last estimate occurred in April 2008, for example, I would like the result to return 16 (rather than 4), assuming Jan 2007 = 1. Thanks in advance for any help. Bob |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding last column in non-contiguous range
Bernie,
Your formula did the trick. Thanks a million! Regards, Bob "Bernie Deitrick" wrote: Bob, =IF(ISERROR(COLUMN(OFFSET(C20,0,MATCH(MAX(C20:N20) +1,C20:N20,1)-1))-2),COLUMN(OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N1 0,1)-1))-2,12 +COLUMN(OFFSET(C20,0,MATCH(MAX(C20:N20)+1,C20:N20, 1)-1))-2) will work if you modify the formulas to return "" and not 0 when they should not be counted in your project schedule. HTH, Bernie MS Excel MVP "Bob" wrote in message ... I have two ranges that contain placeholders for project labor (hours) estimates: C10:N10 (representing Jan - Dec 2007) C20:N20 (representing Jan - Dec 2008) Please note that a project can start and end in any month (i.e., it does not necessarily have to start in Jan or end in Dec.) The following formula calculates the month containing the last estimate (but only in the first range): =COLUMN(OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N10, 1)-1))-2 However, although all my project start sometime in 2007, many do not end until sometime in 2008. Consequently, I need to somehow incorporate the second range into the aforementioned formula. Unfortunately, the MATCH function does not permit non-contiguous ranges. Can anyone tell me how to re-write my formula so that it can include both ranges? Also, if a given project's last estimate occurred in April 2008, for example, I would like the result to return 16 (rather than 4), assuming Jan 2007 = 1. Thanks in advance for any help. Bob |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding last column in non-contiguous range
Bernie,
One more thing (if I may impose upon you): How would I modify your formula to add an additional error check to see if BOTH ranges contained all blanks? And if TRUE, then the result should obviously be zero. Thanks again for your help, Bob "Bernie Deitrick" wrote: Bob, =IF(ISERROR(COLUMN(OFFSET(C20,0,MATCH(MAX(C20:N20) +1,C20:N20,1)-1))-2),COLUMN(OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N1 0,1)-1))-2,12 +COLUMN(OFFSET(C20,0,MATCH(MAX(C20:N20)+1,C20:N20, 1)-1))-2) will work if you modify the formulas to return "" and not 0 when they should not be counted in your project schedule. HTH, Bernie MS Excel MVP "Bob" wrote in message ... I have two ranges that contain placeholders for project labor (hours) estimates: C10:N10 (representing Jan - Dec 2007) C20:N20 (representing Jan - Dec 2008) Please note that a project can start and end in any month (i.e., it does not necessarily have to start in Jan or end in Dec.) The following formula calculates the month containing the last estimate (but only in the first range): =COLUMN(OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N10, 1)-1))-2 However, although all my project start sometime in 2007, many do not end until sometime in 2008. Consequently, I need to somehow incorporate the second range into the aforementioned formula. Unfortunately, the MATCH function does not permit non-contiguous ranges. Can anyone tell me how to re-write my formula so that it can include both ranges? Also, if a given project's last estimate occurred in April 2008, for example, I would like the result to return 16 (rather than 4), assuming Jan 2007 = 1. Thanks in advance for any help. Bob |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding last column in non-contiguous range
Bob,
=IF(ISERROR(Range2),IF(ISERROR(Range1),0,Range1),R ange2) where Range1 is COLUMN(OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N10,1 )-1))-2 and Range2 is 12 + COLUMN(OFFSET(C20,0,MATCH(MAX(C20:N20)+1,C20:N20,1 )-1))-2 This solution always assumes that if Range2 doesn't error out, then Range1 is full and doesn't need to be checked. HTH, Bernie MS Excel MVP "Bob" wrote in message ... Bernie, One more thing (if I may impose upon you): How would I modify your formula to add an additional error check to see if BOTH ranges contained all blanks? And if TRUE, then the result should obviously be zero. Thanks again for your help, Bob "Bernie Deitrick" wrote: Bob, =IF(ISERROR(COLUMN(OFFSET(C20,0,MATCH(MAX(C20:N20) +1,C20:N20,1)-1))-2),COLUMN(OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N1 0,1)-1))-2,12 +COLUMN(OFFSET(C20,0,MATCH(MAX(C20:N20)+1,C20:N20, 1)-1))-2) will work if you modify the formulas to return "" and not 0 when they should not be counted in your project schedule. HTH, Bernie MS Excel MVP "Bob" wrote in message ... I have two ranges that contain placeholders for project labor (hours) estimates: C10:N10 (representing Jan - Dec 2007) C20:N20 (representing Jan - Dec 2008) Please note that a project can start and end in any month (i.e., it does not necessarily have to start in Jan or end in Dec.) The following formula calculates the month containing the last estimate (but only in the first range): =COLUMN(OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N10, 1)-1))-2 However, although all my project start sometime in 2007, many do not end until sometime in 2008. Consequently, I need to somehow incorporate the second range into the aforementioned formula. Unfortunately, the MATCH function does not permit non-contiguous ranges. Can anyone tell me how to re-write my formula so that it can include both ranges? Also, if a given project's last estimate occurred in April 2008, for example, I would like the result to return 16 (rather than 4), assuming Jan 2007 = 1. Thanks in advance for any help. Bob |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding last column in non-contiguous range
Bernie,
Thanks again! I sincerely appreciate all your help. Regards, Bob "Bernie Deitrick" wrote: Bob, =IF(ISERROR(Range2),IF(ISERROR(Range1),0,Range1),R ange2) where Range1 is COLUMN(OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N10,1 )-1))-2 and Range2 is 12 + COLUMN(OFFSET(C20,0,MATCH(MAX(C20:N20)+1,C20:N20,1 )-1))-2 This solution always assumes that if Range2 doesn't error out, then Range1 is full and doesn't need to be checked. HTH, Bernie MS Excel MVP "Bob" wrote in message ... Bernie, One more thing (if I may impose upon you): How would I modify your formula to add an additional error check to see if BOTH ranges contained all blanks? And if TRUE, then the result should obviously be zero. Thanks again for your help, Bob "Bernie Deitrick" wrote: Bob, =IF(ISERROR(COLUMN(OFFSET(C20,0,MATCH(MAX(C20:N20) +1,C20:N20,1)-1))-2),COLUMN(OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N1 0,1)-1))-2,12 +COLUMN(OFFSET(C20,0,MATCH(MAX(C20:N20)+1,C20:N20, 1)-1))-2) will work if you modify the formulas to return "" and not 0 when they should not be counted in your project schedule. HTH, Bernie MS Excel MVP "Bob" wrote in message ... I have two ranges that contain placeholders for project labor (hours) estimates: C10:N10 (representing Jan - Dec 2007) C20:N20 (representing Jan - Dec 2008) Please note that a project can start and end in any month (i.e., it does not necessarily have to start in Jan or end in Dec.) The following formula calculates the month containing the last estimate (but only in the first range): =COLUMN(OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N10, 1)-1))-2 However, although all my project start sometime in 2007, many do not end until sometime in 2008. Consequently, I need to somehow incorporate the second range into the aforementioned formula. Unfortunately, the MATCH function does not permit non-contiguous ranges. Can anyone tell me how to re-write my formula so that it can include both ranges? Also, if a given project's last estimate occurred in April 2008, for example, I would like the result to return 16 (rather than 4), assuming Jan 2007 = 1. Thanks in advance for any help. Bob |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic Ranges using non-contiguous cells and dependent on a cell value | Excel Worksheet Functions | |||
| Non updatable Unique Random Number | Excel Worksheet Functions | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
| Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Linear Mode
