moving breakeven function

[managingcrap]

Had to repost as I didn't attache to excel spreadsheet. I apologize.

Hey guys,

I'm trying to figure out a way that I can get the breakeven cost and quantity, given the initial cost. Please see the picture.

Here's how I wish for it to look like. When the initial cost is 30,000 the breakeven is at C. When the IC is at 55,000, the Beven is at E. What i mean is the calculation is moving depending on where the breakeven falls. Please also note that there's a C and D under the the breakeven totals to indicate which letter the breakeven is made/realized/reached. Also, I need to have the word "TOTAL" to follow the point where the breakeven totals are.

In addition, I did some calculation on cells C11 and C12, and D24 and D25. I did this to get the remainder amount and get the initial cost and the total (to the right of the initial cost).

Everything seems complicated to me and don't know what functions or even where to begin.

Please help.
Thanks in advance.

[k64]

So, I think I can do this, but before I do let me say that I have had bad experiences with designing a worksheet to "move." Do you really need the "Total" to move, or can you make a permanent spot for it? I have wasted many hours trying to make complicated formulas to fix what was really a worksheet set-up issue. I highly recommend a set-up that is static and has headers for each columns with values as rows.

Let me know if you need the worksheet set up this way, or if you can change the set-up to make your job easier.

[ragulduy]

Please find attached example worksheet:
=IF(AND(SUM($B12:B12)=$C4,COUNTIF($B10:B10,"Total")=0),"Total","")
in C10
=IF(C10="Total",SUM($B$11:B11),IF(SUM($C$8:C8)<$C4,C7,IF(B11=B7,($C4-SUM($B$8:B8))/C8*C7,0)))
in C11
and
=IF(C10="Total",SUM($B12:B12),C8/C7*C11)
in C13

then copy across, although I agree with the comments in post#2, why not just put the total fixed in column I?

[managingcrap]

Whichever is easier. I always have a problem with creating something from scratch. But I can easily work and tweat something after seeing the functions. Thanks

[managingcrap]

Please find attached example worksheet:
=IF(AND(SUM($B12:B12)=$C4,COUNTIF($B10:B10,"Total")=0),"Total","")
in C10
=IF(C10="Total",SUM($B$11:B11),IF(SUM($C$8:C8)<$C4,C7,IF(B11=B7,($C4-SUM($B$8:B8))/C8*C7,0)))
in C11
and
=IF(C10="Total",SUM($B12:B12),C8/C7*C11)
in C13

then copy across, although I agree with the comments in post#2, why not just put the total fixed in column I?

You are absolutely a genius. Thanks.

[managingcrap]

How about if there's a cost but no # of house. How will the function change or what will the function be to arrive at the same results that you first provided?

Please see attachment

[ragulduy]

I don't quite understand what the calculation means now. I'd suggest just hard-typing the 0/linking it to the cells above and using the formulas for the rest of it.

[managingcrap]

I don't quite understand what the calculation means now. I'd suggest just hard-typing the 0/linking it to the cells above and using the formulas for the rest of it.

Sorry to confuse you. What I mean is if somebody add a column to the left and just hardcoded the number, how would the function look like so that the TOTAL column (i.e. the TOTAL title still appears, the breakeven # of houses appears, and the cost still equals to the tottal intial cost.

Thanks.

[ragulduy]

I understand what would happen, I don't understand what the calculation would be.

i.e. my understanding was you were multiplying the # of houses by the cost to reach the total. So if the breakeven point was 30000, you were only using a portion of the houses in the section that took it to break even. If you can have 0 houses but still have a value, then my understanding of the calculation is incorrect and I don't know what the formula should be doing.

[managingcrap]

I understand what would happen, I don't understand what the calculation would be.

i.e. my understanding was you were multiplying the # of houses by the cost to reach the total. So if the breakeven point was 30000, you were only using a portion of the houses in the section that took it to break even. If you can have 0 houses but still have a value, then my understanding of the calculation is incorrect and I don't know what the formula should be doing.

I think you are on the right spot. It's me that made a mistake the Cost (not the Initial Cost) should be Payment. What I mean is for example today is 2014 and you're buidling a house in 2014. However, somebody paid you in advance in 2013 forbuilding a house. The 55000 is meant as the money you shell at because the person does not want to pay you full in advance. This means you got money in 2013 for a house to be started in 2014.

[ragulduy]

Ok, so the Initial cost is your costs for building the house, the cost in the 0 house is the "deposit" you received and then D8-DI is the payment you received on completing houses?

I've attached an example which gives you the answers that you put in, it uses the same formulas as before just with the first column hard typed with the 0 values.

[managingcrap]

Ok, so the Initial cost is your costs for building the house, the cost in the 0 house is the "deposit" you received and then D8-DI is the payment you received on completing houses?

I've attached an example which gives you the answers that you put in, it uses the same formulas as before just with the first column hard typed with the 0 values.

Yes. it's a deposit more or less. I see that the additional column is more like a simple link and not necessarily a function.

Thanks.