Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Find First Quarter (2003)

    Hi All,

    I think I'm having some brain damage today but I just can't get this.

    In col A/rows 2-10, I have a list of tasks. Cols B-E have the number of weeks in each calendar quarter that the task needs to be worked with cells B1:E1 having the labels "1Q08", "2Q08", "3Q08" and "4Q08".

    What I'd like in F2:F10 is the first calendar quarter that the task needs to be worked.

    For example, if A2 is design and work is done as 0 (or blank), 3, 4, 0 in B2:E2 (ie 0 weeks of work on design in 1Q08, 3 weeks in2Q08, 4 weeks in 3Q08, and 0 weeks in 4Q08), I'd like F2 to show 2Q08.

    I know I could do a nested IF since I only have 4 quarters to examine but that seems very inelegant. For each task, I need to search for the first non-zero numeric entry (the entry could be blank or 0 if no work is done) in the task's row in cols B:E and return in F the quarter from row 1 in which work is to be done. If the task has no entries, something like "no work" should be returned.

    TIA

    Fred

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find First Quarter (2003)

    In F2:
    <code>
    =IF(MIN(IF(B2:E2>0,COLUMN(A)))>0,OFFSET($A$1,0,MIN(IF(B2:E2>0,COLUMN(A)))),"No work")
    </code>
    This is an array formula, confirm with Ctrl+Shift+Enter. Then fill down.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Find First Quarter (2003)

    Thanks Hans.

    I wasn't even close. I was trying something using MATCH or SEARCH. Never occurred to me to use MIN and COLUMN.

    Fred

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •