1. 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. 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. 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
•