First occurrence in a series

  • Thread starter Thread starter Bill Carlson
  • Start date Start date
B

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
 
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.
 
Bill

Another option:

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

To be entered with <Shift><Ctrl><Enter>
 
A better option though.

Leo Heuser said:
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
Click to expand...
 
Clever Leo..

--

Regards,

Peo Sjoblom

Leo Heuser said:
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
Click to expand...
 
Leo Heuser said:
Another option:

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

To be entered with <Shift><Ctrl><Enter>
Click to expand...

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

=MATCH(TRUE,--A2:L2>0,0)
 
[...]
And another array formula alternative in case this would ever be needed
deeply nested in a larger formula.

=MATCH(TRUE,--A2:L2>0,0)
Click to expand...

Agreed, with the -- omitted.
 
Aladin Akyurek said:
Agreed, with the -- omitted.
Click to expand...

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.
 
Harlan Grove said:
Always test - especially when second-guessing my responses. With standard
settings (transition formula evaluation disabled),
Click to expand...

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.
Click to expand...

Easy.
 
Back
Top