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
multiproduct.

thanks

## Related Results

### Chart/Graph Break Even Point Different Than Formula

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!

### Help capping a royalty once break-even point is reached

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

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

spends
- 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

### Chart showing break even point

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.

http://img528.imageshack.us/img528/9...eenshotrv1.jpg

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.

### Formula for counting charachters in a cell ?

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

### Finding a break even point in a series of data...

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.

### Break Even Analysis Formula Issue

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.

### Break Even Analysis problem

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.

### Highlighting break even cell

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.

### Approximate break even point with LOOKUP

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.

### Calculate Break Even Point Of Ticket Sales

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

LJK

### Formula for row count in a filtered column

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?

### Formula for counting items in a 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.

### Formula for recognizing which quarter a date is in

Hello,

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.

Thanks

### Formula for random shuffle between a range (example) b4,b78

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

### Excel formula for counting text in a certain color?

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

### Formula for dividing and rounding a number with multiples of 500

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?

Best Regards,

Mojito

### Formula for converting info in a column into a list...

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.

### Goal seek to find break even but not working

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.

### Formula for subtracting a percentage

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

### Break Even Point - return the year

Hi,

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.

H

### Formula for doing something when a cell has a formula but returns a blank.

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

### Break Even Analysis

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

### Write a formula for every cell in a column for the total number of rows of data

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.

Thanks.

```Sub FormatAccessOutput()

Application.ScreenUpdating = False
Cells.Select
Range("D5").Activate
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.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 =
"=(RC[-4]+RC[-3]+RC[5]+RC[-17])/(RC[1]+RC[2])"
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("H1").Select
Selection.Copy
Range("G1").Select
ActiveSheet.Paste
Range("G2", Range("G2").End(xlDown)).FormulaR1C1 = "=(RC[1])"
Columns("N:N").Select
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 =
"=(RC[-5]+RC[-4]+RC[5]+RC[-22])/(RC[1]+RC[2])"
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("BB:BD").Select
Selection.Cut
Columns("L:L").Insert Shift:=xlToRight
Columns("M:N").Select
Selection.Cut
Columns("R:R").Insert Shift:=xlToRight
Columns("J:J").Select
Selection.Cut
Columns("D:D").Insert Shift:=xlToRight
Range("N2").Select
Selection.AutoFilter
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