Find breakeven point in a stream of cashflows, then calculate difference.

[justinjames]

I have a stream of cashflows going all across row 37, where each column represents a month (ie. B37 is cashflow in january, C37 february, etc.), and I want to have a function that finds what month these cashflows go from negative to positive, and then calculate the difference between those two months (the last negative month, and the first positive month). I currently have:

=INDEX($B$2:DQ$2,MATCH(TRUE,B37:DQ37>0,0))


Which is successfully returning the month of break even, but now I want it to also calculate the difference between the last negative and first positive month. To further complicate things, the "break-even point" will not always be the same, as it changes based on the input parameters. For example, if the cashflow in cell F37 is negative then then the cashflows starting with G37 are positive, I want to return the month associated with G37 (say, cell G2) and then calculate the difference between cell F37 and G37. However it will not always be F37 & G37...depending on input values on a different sheet, the point where the value goes from negative to positive might be I37 and J37.

I feel like there is a way to do this using SUMIFS in combination with INDEX and MATCH, but I just cannot pinpoint exactly how to do it.

Thanks.