Re: Finding last column in non-contiguous range

Tech-Archive recommends: Speed Up your PC by fixing your registry

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:N10,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" <Bob@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CA151D9D-F5FE-40A6-BF80-794B26C6A38B@xxxxxxxxxxxxxxxx
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




.

Relevant Pages

  • Re: XOR drawing on Tk canvas?
    ... > Is there some trick I'm missing here, or am I going to have ... > to modify the PhotoImage directly to get my hilighting? ...
    (comp.lang.tcl)
  • Re: fdisk on mounted disks ?
    ... does anyone know the trick to be used to modify ... > the slices on a mounted disk? ...
    (freebsd-stable)
  • Re: Mail Rules
    ... What I have done is just re-create the rule and that does ... the trick. ... > One of my mail rules will not allow me to modify it. ... > Rules discription is grayed out and the MODIFY and REMOVE ...
    (microsoft.public.outlook)
  • Re: SBS 2003 blocks incoming traffic from internet gateway
    ... changing - you don't have to modify the e-mail portion. ... a combination of running CEICW, ... Ah, that's the trick. ...
    (microsoft.public.windows.server.sbs)
  • Re: Excel Formula: Text equivalent to MODE?
    ... "Bernie Deitrick" wrote... ... >How should the OP modify that answer to meet their requirement that only A1, ... The original formula in question, ... to bottom-post and snip is sublime. ...
    (microsoft.public.excel.misc)