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

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

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

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

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

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

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