Break Even Type Formula

[MikeR320]

Long time lurker of this forum, so I already owe many thanks but I have one issue I wasn't able to find...

Have a Pro-Forma I made which has months (in text) in the 3rd row and cumulative cash flows in the 50th row. What I would like, is to get a formula which dynamically returns what month I am in where the 50th row = x (but doesn't have to be exactly x) I have tried index match to no avail

e.g.
Row 3: January| February| March| April|



Row 50: 1 | 20 | 26 | 40

Formula to return March if I am looking for when row 50 = 25; would be better not to be a macro

Thanks, I can post an example if that helps.

 

[bbott]

Welcome to the board.

This may not be exactly what you need, but maybe will get you in the right direction. You can replace the 25 with a cell reference so you don't always need to change the formula.

=INDEX(A3:L3,,MATCH(25,A50:L50,1)+1)

 

[pjmorris]

Hi Mike and welcome to the forum.

It would help if you can define more clearly what associates a value with a month - is it simply the closest figure or is it the month in which the figure in Row50 exceeds you value of interest. If the latter then if your figure of interest is, say, 60 then no month would be identified.

Regards

 

[MikeR320]

Hey Peter, thanks for the quick response, and I apologize for my vagueness. Values are ascending from time 0; and let's assume my figure of interest will always be within my time period. I would also like it to be the very first month in which my value of interest is exceeded.

 

[bbott]

Hey Peter, thanks for the quick response, and I apologize for my vagueness. Values are ascending from time 0; and let's assume my figure of interest will always be within my time period. I would also like it to be the very first month in which my value of interest is exceeded.

The formula I posted above should work for you.

 

[MikeR320]

bbott! Sorry for unknowingly overlooking your response while I replied to Peter there. You are a true life saver. Thank you so much for the rapid reply and ingenious help. You're the best.

 

[pjmorris]

I need say no more!

 

[bbott]

bbott! Sorry for unknowingly overlooking your response while I replied to Peter there. You are a true life saver. Thank you so much for the rapid reply and ingenious help. You're the best.

You're welcome.