Index (?) to get payback period?

[Texas Longhorn]

Hi,

I have a workbook that models out production for an oil well. Column A is "Period", measured in days. Column F is "Cumulative NPV", measured in dollars. In cell N1, I have an initial well cost ($800,000). In cell O1, I would like to return the period number from Column A at which the cumulative NPV in column F first exceeds the initial well cost in cell N1.

I've been trying combinations of INDEX and IF, but have not had any luck. Any help is much appreciated.

Thanks,

Bill

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=INDEX(A1:A100,MATCH(TRUE,SUBTOTAL(9,OFFSET(F1:F100,,,ROW(F1:F100)-ROW(F1)+1))>N1,0))

Adjust the ranges accordingly.

Hope this helps!

 

[hatman]

=index(a:a,min(if(f1:f100>n1,row(f1:f100),65536))) Confirmed with Ctrl+Shift+Enter... adjust range as needed, but do not select all of colmn F

 

[Domenic]

Oops, it looks like I missed the key part 'Column F is cummulative NPV'. My formula can be amended as follows...

=INDEX(A1:A100,MATCH(TRUE,F1:F100>N1,0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[hatman]

Oops, it looks like I missed the key part 'Column F is cummulative NPV'. !

Whew

I thought I was the one who missed something.

 

[Texas Longhorn]

Perfect. Thanks to both of you,

Bill