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