Break Even Calculations

[sav1979]

Hi all,

I have a row which shows date by month and then a row which shows cash flow. I would like a formula to calculate the break even point in the cash flow row and return the month which this occurs. I would also like to show the number of units required to break even.

Many thanks in advance for your assistance.

[Kaper]

Array formula (committed with Ctrl+Shift+Enter) shall do:

=INDEX(A1:Z1,1,MIN(IF(A2:Z2>0,COLUMN(A2:Z2),"")))

A1:Z1 dates, A2:Z2 CFs

second one (again array formula):

=MIN(IF(A2:Z2>0,COLUMN(A2:Z2),""))

adjust only first range if your data does not start with column A - for instance:

=MIN(IF(B2:AA2>0,COLUMN(A2:Z2),""))

PS. Have you noticed this in http://www.excelforum.com/forum-rule...rum-rules.html :

Want to get your question answered quickly?
...
Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

It makes answering much easier/quicker.

[sav1979]

Hi Kaper,

Many thanks for your response and apologies for not attaching. I have done so now. Using the formula provided, the break even date appears to incorrect. On the input tab, cell C9 is where I have added this formula but it shows August 15 as break even date when it is actually May 15. I am also not sure of the second part of the calculation to work out cell C8.

Many thanksAttachment 291425