Finding First Positive Value in A row

[tomastriginer]

Hello everyone,

To summarise:

I am a quite expereinced excel user and usually end up fixing my problems but this time I have to ask online.

Does anyone know how to use the following formula using different intervals along a single row?

=MATCH(1,INDEX(1/($B51:$LM51>0),0),0)


To understand my issue in more detail to be able to offer me other alternatives please read the following:

I am creating a cashflow model which starts in month 1 and goes on till month 300. I am expecting to make one of the outcomes of the model the month number in which the business would breakeven. Keep in mind that the breakeven point will vary depending on many variables.

To achieve this I tried to apply the following formula which finds the first positive value in a given range.

=MATCH(1,INDEX(1/($B51:$LM51>0),0),0)

This would work if it wasn't for the fact that I need to put an empty column every 12th months. Because of these empty columns the outcome I get is always higher. For example if Breakeven was in moth 26 the outcome of the formula would be 28 instead because of the two empty columns in between. I tried to use the formula above using different intervals but it gave me a value error.
How can I solve this problem?
An alternative I thought about but can't execute is to enter the whole row and then just exclude the empty cells somehow.

Would really appreciate your help,

Tomas

 

[mart37]

=match(1,index(1/($b51:$lm51>0),0),0)-int(match(1,index(1/($b51:$lm51>0),0),0)/13)

 

[footoo]

Post #12 from here : https://www.mrexcel.com/forum/excel...p-first-positive-non-zero-number-range-2.html