Goal seek as a formula

I am wanting to use a goal seek answer as a formula in another cell instead of actually "changing" a cell. In other words, I want the Goal Seek "answer" to show up in a different cell.

For instance, let's say I have the following

A B
Sales Price x
Revenue x
Expenses x
Profit x

Break-Even Profit x

I want to find out what sales price x would have to be in order to make the profit x 0, but I want that answer displayed as the Break-Even Profit x. I wouldn't really want to do this with a macro, because I want it to update every time change any x, but I can't think of how else to do it?

Thanks in advance,
Chris

If I understood correctly, you should be able to do it using simple formulas. excel help would be good place to start.

Thanks
Ajay

There is no simple “goal seek” formula in excel
Mayby “ menu----data---goal seek “ can solve you （EXCEL 2003） question,but when you change the figures (except sales),you have to run the goal seek again.

Andrew, I think you're right, but I'm hoping that there is some way to do it.

ajaykgarg, I have attached an example. What I did was a goal seek by changing cell B20 to 0 by changing cell B5, and the answer, I copied to cell C5. What I want, is that answer to always be displayed as the Break Even Sales Price in cell B22. When I change a cost, for example the Fees, I want it to re-calculate what the Break-Even Sales Price would be. This, I don't believe, can be done with simple formulas as you suggest. Am I missing something?

Thanks

You are right. I mis-understood your requirement. Sorry.

Does anyone know how to do this with VB?

Chris,

You have a great big algebra problem here.

Given that Revenue = Price * Units Sold
Given the Cost of Goods (Expenses) = Costs * Units Sold
Profit = Revenue – Cost of Goods Sold

The calculation for Break Even NEW Price =
(((Old Price * Old Units) – (Old Costs * Old Units)) + (New Costs * New Units)) / (New Units)

I put together a small worksheet. Change the new costs and/or new units to get identical profits based on a changing Price (in C2).

Roger!

Thanks for the example. That seems to work, where I can change certain values and the break even price is solved for, but having two rows of columns for each product probably isn't the best solution, as I've got a hundred columns.

Also, I want to be able to change any number in my sheet, expenses, shipping, unit sales, etc, and be able to see what price I need to sell an item at in order to break even, given everything else. The only way I can think of to do that is to have a running goal seek cell on the price cell that shows what it needs to be in order for profit to be 0. I've read around on google and found that there are sites using the goal seek in a macro somehow, but I'm not able to get it working...

Thanks

See if the attachment does what you are looking for.
However, in your example workbook from post #4 i noticed that when the "number of items" goes up to 200, you seem to make a profit even with a selling price of $0?

Wow, that's the ultimate goal I guess, even though I know it takes just a minute to do it. Is there a way to re-write this so that it solves for the Break Even Sales Price but only changes it in B22 and not in B5. Can anyone do this?

I also fixed the Break Even Sales formula, as it was way off.

As for your question, the reason there is profit at sales price 0 is because the buyer is charged the shipping amount as well, in cell B6, and the shipping there is more then the shipping out costs. Also, we can't look at a combined price+shipping price to do the goal seek because they are both treated differently when it comes to costs in the expenses section.

Although it looks neat, I wonder if there would be a way to 'clean this up' so we couldn't see it actually doing the math when the macro runs? If this were done perfectly, it would instantly calculate the answer for break even price, and show it in the Break Even Price cell, B22, without having to click a Run Macro button.

I've always kicked myself when I almost said "it isn't possible in Excel" because it almost ALWAYS is, somehow....but I just don't have all of the answers, unfortunately. Thanks a lot for your help either way, this is good work.

Chris