Function resulting to the exact month when breakeven exists

HiGuys,

I'm trying to figure out a function that allows to me to determine the exact month when I will breakeven. For example, my total net income for the 2 quarters of the year is 1050 and my investment is 500. If I have an income in Jan of 300, 300 in Feb, and 200 in March, then the function will spit out Feb as the breakeven month.

Please see attachment. Thank you guys.

Try this array formula**:

=INDEX($B2:$G2,MATCH(TRUE,SUBTOTAL(9,OFFSET($B5,,,,{1,2,3,4,5}))>=C8,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

The array {1,2,3,4,5} represents the number of columns of data you have. If your real file has "many" columns we can express that in a more dynamic fashion.

Originally Posted by managingcrap

It works perfectly. By any chance can you explain me the MATCH function area? I don't quite get it. thanks.

So, the Match function itself returns a reference to the position that was met...
... wait... what?

So if you're looking for the value C in an array {A,B,C}, match will Return 3.
The Index function then uses that 3 to select the 3rd item in the array. In your example, the third item in the array is March.

Where the real magic is happening is within the Subtotal equation, which is determining when the subtotal will be above C8, which is your investment.

Does that help break it down a little bit for you? If not, feel free to ask!

with helpcolumns, see the attached file.

We use the SUBTOAL function to get an incremental sum from the range.

300...300...200...150...100

The incremental sums would be:

=300
300+300=600
300+300+200=800
300+300+200+150=950
300+300+200+150+100=1050

So we have this array of incremental sums: {300,600,800,950,1050}

We use the MATCH function to tell us which of those sums is greater than or equal to the investment amount.

If the investment amount (C8) was 722 then:

MATCH(TRUE,{300,600,800,950,1050}>=722,0)

300 is not >=722 so = FALSE
600 is not >=722 so = FALSE
800 is >=722 so = TRUE

The result of the MATCH function is 3. It returns the relative position number where the array test was TRUE.

This result is then passed to the INDEX function:

INDEX(B2:G2,3)

Meaning: return the cell value from the 3rd cell in the range B2:G2.

B2 = 1st cell
C2 = 2nd cell
D2 = 3rd cell

Result = D2 = Mar

I see that I made a minor error in the formula.

=INDEX($B2:$G2,MATCH(TRUE,SUBTOTAL(9,OFFSET($B5,,,,{1,2,3,4,5}))>=C8,0))

The actual range should be B2:F2.

Originally Posted by Tony Valko

Try this array formula**:

Actually, as written, the formula does not need to be array entered. Just a normal enter will do.

I was anticipating having to replace the array constant with a dynamic calculated array which would require array entry.

Not real sharp today. I'm having one of those days!

I need an ice cream break!