Free Microsoft Excel 2013 Quick Reference

formula for break-even point for a multiproduct

I would like to get the formula for calculating the break-even point for a


Post your answer or comment

comments powered by Disqus
hey guys. we're supposed to find the breakeven point for this company. if you look at the table i've made, it seems like the break even point is between 17000 and 18000 units produced. i made a more precise chart and calculated the point to be 17535... but on the graph as you can see the point is 18000... can anyone tell me why that is? if you need more info just let me know. thanks!

Hello I am trying to work out how to cap a royalty accounting once break even point has been reached

- revenue source 1 (on which a 15% royalty has to be paid until breakeven point)
- revenue source 2
- revenue source 3

- spend 1: 15% royalty payment on revenue source 1 (until breakeven point)
- spend 2
- spend 3

Total (sum income - sum spends)

for example if at breakeven the total royalties to be paid amount to £1000, spend 1 would be capped at £1000

Thank you in advance for your help

I need to produce a graph to show the break even point of a coach trip, so i have the outgoing money the company organising the trip would have to pay, and how much the company would get payed depending on how many tickets they sell. Here is a screenshot to make that a bit clearer.

So what i need isa graph that has a vertical line on it that will show what the trip is costing the comapny. Then i need a line diagonly showing the money being made by tickets so that the graph shows the break even point. I can produce a graph using just the ticket data but i cant work out how to get the total cost figure to appear as a line on the graph.

Sorry if this is a bit confusing, its hard to explain. If you need more information then just ask, any help appricaited.

could anyone tell me the formula for counting charachters in a cell

I am doing break-even analysis on a set of data. There is a time series and a number associated with that time. The number starts off negative and eventually becomes positive. I need a formula that will calculate the crossover point, the time at which the data becomes positive.

For example,

1 -10000 1 -12000
2 -8000 2 -10000
3 -4000 3 -6000
4 -2000 4 -3000
5 0 5 -1000
6 1000 6 500
7 10000 7 2000
8 15000 8 6000
9 20000 9 8000
BE is 5 BE is 6

The forumla should execute as quickly as possible. The formula will be applied to an extraordinary amount of data...millions of times.

Thanks for any help I can get.

I have a quick question about a function regarding a workbook assignment in which a
break even point in years must be calculated.

We must calculate the break even point in years based on the Revenue and Expense amounts given with each different growth rate.

I can do things algebraically, however I'm stuck since only a formula can be used to fill in the missing cells.

Any insight would be greatly appreciated.

Hey Everyone I am a new intern at a company and I am trying to work on a spreadsheet that will do some calculations in excel but I am having trouble with the calculations and would be greatful for any advice.

Here is the situation in simplest terms to try and help you guys understand the problem. I have a machine that cuts metal. But it cuts metal at a decreasing rate of efficiency and eventually needs to be replaced. Here is an example of what I mean. A brand new machine cuts metal at an efficiency of 2 pounds of metal used per foot of metal cut. Eventually the machine becomes less efficient and it requires 3 pounds of metal to cut one foot and so on. Metal is expensive, so as more and more metal is used to cut one foot, the proccess is becoming less and less efficient and more and more expensive. Eventually we are better off just purchasing a new machine, which for this example lets say costs 10,000 with a scrap value of 2000. The rate at which the machine become less efficient is constant at .01 pounds more of metal required per foot cut after every 1000 feet cut.

Here is all the required informaton

Cost of new machine = $10,000
Salvage value of used machine - $2000
Rate of efficiency of Brand new machine = 2 pounds of metal required per foot cut
Rate at which machine becomes less efficient = .01 lbs of metal per foot more required every 1000 feet of metal cut
cost of metal per foot = $2

So if you subtract the salvage value of $2000, the cost in the end of buying and using a new machine is $8000. So what I need to calculate in excel is the break even point (in feet produced) when the cost of using more and more metal every foot produced due to a less efficient machine equals the cost of a new machine ($8000). Basically, When is the best time to buy a new machine.

If anyone has any advice I would greatly appreciate it.

Hey everyone, I have the following issue that i can't figure out.
I ve been trying from a given cash inflow to identify, which cell is the break even point automatically and highlight it automatically too.
The list starts with a big negative number and continues with positive numbers, at some point the balance becomes 0 and then starts to be positive.
How can I identify this cell automatically (when the balance starts beeing positive? Suppose the list in on range A2:A32 with A2 beeing the fist negative number.
I really appreciate any help you might give me. Ive tried with INDEX and MATCH formulas but had not luck so far.
Thanks in advance.

Please view the attachment for more info (tab: klokken-expert)

I have fixed costs (C9)
I have gross margins (C21:C56) that match certain week numbers (A21:A56)

What I would like to know is in what week we break even. So basically it should add up the margin for each week (ignore #N/A) and return the week number when the SUM of the previous weeks equals or > than 0.

I want to use Solver to solve the following problem, but I can’t work out how and its driving me crazy.

I have to organise a prize giving dinner so I know I need to work out my expenses and make sure I cover those.

I have to break even with 200 ticket sales, but I can sell a maximum of 300 tickets.

Non members pay full ticket price and member pay 75% of the ticket price.

Can someone explain to me how I can set the ticket price using solver?

Thank you


I need a formula that will count the number of rows in a filtered column. For the sum, I use the subtotal formula. Is there a similar formula for counting the number of rows in a filtered column?

Hello. I am collating data in a survey and I have columns of data with the answers 1, 2, 3, 4, or 5. I would like to enter a formula at the end of a column to calculate how many cells contain the answer 1. Then in another box I want to list how many cells contain the answer 2, etc. I have been highlighting the column and using Find All in the Edit menu, but I am sure there is an easier way. I know how to count entries in a column as a whole, but cannot figure out how to look for a certain entry. Any help you can provide would be hugely appreciated! Thanks.


I am looking for a formula that will tell me the quarter that a specific date in another cell is in. So for example:

In cell B2 I will have an announcement date such as August 27, 2007. In cell A1 I would like to have a formula that looks at the date in cell B2 and returns the value "Q3". I'm not sure how to put date ranges in an IF formula for this so any help would be appreciated.

So Q1 would be between January 1 - March 31, Q2 between April 1 - June 30, Q3 between July 1 - September 30, and Q4 between October 1 - December 31.


Does any one have a formula for a random shuffle between a range? This is
needed to draw random teams for a golf tournament.

Is there a formula for counting items that have been marked in a certain color?

Hi to all,

I'm new here and I would like to ask you guys for some help solving this problem. Here we go

I would like to have a formula that could divide a number in 4 and could give me the result rounded down in multiples of five hundred, as an example:

- 13000 / 4 = 3250 the result that i would like to obtain rounded would be 3000
- 7250 / 4 = 1812,5 the result that i would like to obtain rounded would be 1500

Is it possible to make a formula for that?

Thanks in advanced,

Best Regards,


I hope no one has already posted this question and i'm just not bright enough to find the thread!

Basically, I have a client and project database which I update constantly. I do everything manually now, but it's just too time consuming and I know just enough about formulas in excel to impress someone who has never seen excel. This one is beyond me.

Worksheet 1 has the projects and pertinent information (because that is updated most often).
Worksheet 2 contains info about materials which come into the shop in reference to the projects.
Worksheet 3 contains contact info for the clients.

I'm using a simple list manager workbook to take care of most of the information.

On worksheets 1 & 2, there is a "client name" column on which I use a list wizard function to simply chose the name from a drop-down menu. I want the "client name" column on worksheet 3 to be a master list of my clients for those other worksheets.

So, again, for my own brain, how would I craft a formula for the "client name" columns on worksheets 1 & 2 to access the names from the "client name" column on worksheet 3 and use them as the "list"?

...if that makes sense.

I want to understand this financial model in greater detail.

I am trying to work out the break-even point of sales.
it does not matter if I use best case scenarion (BC) on model or worst case scenario (WC).

I have tried using goal seek but it has not been working for me.

The model is in .doc and also the Excel document is zipped.

Please might I have help with a formula for the following:
Col A contains 25
Col B contains 28
Col C contains 15

I need to add 25 to 28 and from the total minus 15 as a percent, i.e. AplusBminus15%
I'm sure it's elementary but I'm grateful if anyone can help
Many thanks


I am trying to return the year where benefits are greater than costs.

I have three columns: The first is the year (e.g. 2000, 2001...2099), in number format. The second is the costs and the third is the benefits.

On another worksheet, I have a number of assumptions which feed into the costs and benefits. As I mess with these, I would like to see how the 'break even' year changes.

Thanks heaps in advance,

I am looking for an If() formula that says that if a cell is blank then do this, otherwise that.

The issue is that the "blank" cell has a formula in it. It is just returning nothing because I told it to, due to the fact that it did not meet some criteria.

Isblank() formula does not work because the cell has a formula in it.

How do I deal with this. Thanks

How do I create the break even point in excel??? Thank-you!!!

Below is my code -
The part of the codein blue font is for writting formulas in the cells. The current code is writing formula for every cell in the column until the last cell. I want to write the formula for only the number of rows that actually contain the data.

Can anyone help me please??


Sub FormatAccessOutput()

    Application.ScreenUpdating = False
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

'Insert formulas
    Range("L2", Range("L2").End(xlDown)).FormulaR1C1 = "=RC[-5]/RC[-1]"
    Range("O2", Range("O2").End(xlDown)).FormulaR1C1 = "=RC[-3]/RC[-1]"
    Range("Q2", Range("Q2").End(xlDown)).FormulaR1C1 = "=RC[-10]*RC[-1]"
    Range("S2", Range("S2").End(xlDown)).FormulaR1C1 = "=(RC[10])/(RC[6]+RC[7])"
    Range("X2", Range("X2").End(xlDown)).FormulaR1C1 =
    Range("AH2", Range("AH2").End(xlDown)).FormulaR1C1 = "=RC[-3]-RC[-27]"
    Range("AJ2", Range("AJ2").End(xlDown)).FormulaR1C1 = "=(RC[-5])/RC[-1]"
    Range("AK2", Range("AK2").End(xlDown)).FormulaR1C1 = "=RC[-3]/RC[-2]"
    Range("AM2", Range("AM2").End(xlDown)).FormulaR1C1 = "=IF(RC[-1]="""",
""N"", (RC[-1]*(RC[-14]+RC[-13])-RC[-19]-RC[-18]-RC[-10]))"
    Range("AO2", Range("AO2").End(xlDown)).FormulaR1C1 = "=IF(RC[-1]="""",
""N"", RC[-10]-RC[-1]*RC[-6])"    
'Change the format of columns AI and Z to number with no decimal places

    Range("AI:AI,Z:Z").NumberFormat = "0"

'Change the format of columns L,O,Q,S,X,AJ,AK to number with two decimal places
    Range("L:L,O:O,Q:Q,S:S,X:X,AJ:AJ,AK:AK").NumberFormat = "0.00"

    Columns("G:G").Insert Shift:=xlToRight
    Range("G2", Range("G2").End(xlDown)).FormulaR1C1 = "=(RC[1])"
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight
    Range("N1").FormulaR1C1 = "=(RC[-1])"
    Range("N2", Range("N2").End(xlDown)).FormulaR1C1 = "=(RC[-7]/RC[-2])"
    Columns("R:R").Insert Shift:=xlToRight
    Range("R1").FormulaR1C1 = "=(RC[-1])"
    Range("R2", Range("R2").End(xlDown)).FormulaR1C1 = "=(RC[-4]/RC[-2])"
    Columns("U:U").Insert Shift:=xlToRight
    Range("U1").FormulaR1C1 = "=(RC[-1])"
    Range("U2", Range("U2").End(xlDown)).FormulaR1C1 = "=(RC[-14]*RC[-2])"
    Columns("AC:AC").Insert Shift:=xlToRight
    Range("AC1").FormulaR1C1 = "=(RC[-1])"
    Range("AC2", Range("AC2").End(xlDown)).FormulaR1C1 =
    Columns("AN:AN").Insert Shift:=xlToRight
    Range("AN1").FormulaR1C1 = "=(RC[-1])"
    Range("AN2", Range("AN2").End(xlDown)).FormulaR1C1 = "=(RC[-4]-RC[-33])"
    Columns("AR:AR").Insert Shift:=xlToRight
    Range("AR1").FormulaR1C1 = "=(RC[-1])"
    Range("AR2", Range("AR2").End(xlDown)).FormulaR1C1 = "=(RC[-4]/RC[-3])"
    Columns("L:L").Insert Shift:=xlToRight
    Columns("R:R").Insert Shift:=xlToRight
    Columns("D:D").Insert Shift:=xlToRight
    ActiveWindow.FreezePanes = True
    'Highlight columns H,O,U,X yellow
    Range("H1", Range("H1").End(xlDown)).Interior.ColorIndex = 36
    Range("O1", Range("O1").End(xlDown)).Interior.ColorIndex = 36
    Range("U1", Range("U1").End(xlDown)).Interior.ColorIndex = 36
    Range("X1", Range("X1").End(xlDown)).Interior.ColorIndex = 36
    'Highlight columns AM,AQ,AU light green
    Range("AM1", Range("AM1").End(xlDown)).Interior.ColorIndex = 35
    Range("AQ1", Range("AQ1").End(xlDown)).Interior.ColorIndex = 35
    Range("AU1", Range("AU1").End(xlDown)).Interior.ColorIndex = 35
    'Highlight column AE light blue
    Range("AE1", Range("AE1").End(xlDown)).Interior.ColorIndex = 33
    'Highlight column AF blue
    Range("AF1", Range("AF1").End(xlDown)).Interior.ColorIndex = 34
    'Highlight column AD grey
    Range("AD1", Range("AD1").End(xlDown)).Interior.ColorIndex = 15
    Application.ScreenUpdating = True
End Sub

need formula for validating data in a cell. function required is for allowing
only multiples of 10

No luck finding an answer? You could always try Google.