Cumulative Breakeven points for revenue

[tensaix]

I have a spreadsheet with revenue information, and I am trying to calculate the cumulative break even point.

Column A      Column B
FY11           -373
FY12           -583
FY13            257
FY14            150
FY15            700

In column B, I have varying figures for each Fiscal Year, and FY11 is (-373) and FY12 is (-583). Together they are (-956). Although the first year the revenue goes positive is in FY13, which is 257, I am looking to build a formula that will say the cumulative break even point is in FY15 or year 4, since 257+150+700 is greater than FY11 and FY12. Is this possible?

 

[Domenic]

To return FY15, try...

=INDEX(A2:A6,MATCH(TRUE,SUBTOTAL(9,OFFSET(B2:B6,,,ROW(B2:B6)-ROW(B2)+1))>=0,0))

To return 4, try...

=MATCH(TRUE,SUBTOTAL(9,OFFSET(B2:B6,,,ROW(B2:B6)-ROW(B2)+1))>=0,0)

Note that both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

 

[tensaix]

phenomenal! works perfectly!! Thank you!!