First occurrence in a series

[Bill Carlson]

Excel 2003

In our business forecasts we are commonly asked, 'what is the first month of
the forecast that the company makes a profit'?

Column A B C D E F

Month 1 2 3 4 5 6

Profit (Loss) -2 -1 2 1 3 -1


Month 3 is my result. I've been futzing with match, index, hlookup, large,
countif -- only seem to get the highest profit in the series, the number of
profitable months, or garbage, not the first occurrence.

As always, help is much appreciated,

Bill Carlson

 

[Aladin Akyurek]

Let A1:G2 house the sample...

{"Month",1,2,3,4,5,6;"Profit/Loss",-2,-1,2,1,3,-1}

=INDEX(A1:G1,MIN(IF(B2:G2>0,COLUMN(B2:G2))))

which must be confirmed with control+shift+enter instead of just enter.

 

[Leo Heuser]

Bill

Another option:

=MATCH(1,SIGN(A2:L2),0)

To be entered with <Shift><Ctrl><Enter>

 

[Aladin Akyurek]

A better option though.

Bill

Another option:

=MATCH(1,SIGN(A2:L2),0)

To be entered with <Shift><Ctrl><Enter>

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

month

 

[Leo Heuser]

Thanks, Aladin

LeoH

 

[Peo Sjoblom]

Clever Leo..

--

Regards,

Peo Sjoblom

Bill

Another option:

=MATCH(1,SIGN(A2:L2),0)

To be entered with <Shift><Ctrl><Enter>

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

month

 

[Leo Heuser]

Thanks, Peo

Regards
LeoH

Followup to newsgroup only please.

 

[Harlan Grove]

Another option:

=MATCH(1,SIGN(A2:L2),0)

To be entered with <Shift><Ctrl><Enter>

And another array formula alternative in case this would ever be needed
deeply nested in a larger formula.

=MATCH(TRUE,--A2:L2>0,0)

 

[Aladin Akyurek]

[...]

And another array formula alternative in case this would ever be needed
deeply nested in a larger formula.

=MATCH(TRUE,--A2:L2>0,0)

Agreed, with the -- omitted.

 

[Harlan Grove]

Agreed, with the -- omitted.

Always test - especially when second-guessing my responses. With standard
settings (transition formula evaluation disabled),

=MATCH(TRUE,{"a";-1;2}>0,0) returns 1

=MATCH(TRUE,{-1;"a";2}>0,0) returns 2

however, both

=MATCH(TRUE,--{"a";-1;2}>0,0)

and

=MATCH(TRUE,--{-1;"a";2}>0,0)

returns 3. Replace the array constant with a range containing the same
values in the same order ti check if range evaluation has any effect. Left
as an exercise to explain why.

 

[Aladin Akyurek]

Always test - especially when second-guessing my responses. With standard
settings (transition formula evaluation disabled),

I wasn't second-guessing. Testing instead of second-guessing would be
advisable for any response, although bold hunches, statements should not be
ruled out either... That said, I just didn't include possible text-values
into my evaluation --- an unfortunate omission, especially in the light of a
range which houses formulas as is the case with the OP.

=MATCH(TRUE,{"a";-1;2}>0,0) returns 1

=MATCH(TRUE,{-1;"a";2}>0,0) returns 2

however, both

=MATCH(TRUE,--{"a";-1;2}>0,0)

and

=MATCH(TRUE,--{-1;"a";2}>0,0)

returns 3. Replace the array constant with a range containing the same
values in the same order ti check if range evaluation has any effect.
Yep.

Left as an exercise to explain why.

Easy.

 

[Bill Carlson]

Perfect -- extremely useful and as always, very educational.

Thanks all!

Bill

 

[Leo Heuser]

You're welcome, Bill, and thanks
for the feedback.

LeoH