Sale price or Margin

[bravors]

Hi all

I have created a simple spreadsheet to allow me to enter a sales price for a product. The spreadsheet has a cost price and I then key a sales price. In the next column it shows the margin as a percentage using (sp-cp/sp). So this works fine but there are times where I have a margin in mind first and I want it work out the sales price for me instead so sort of reversing what I have already. I could do a new spreadsheet which allows me to type in the cost price and the margin and then show the sales price but is there a way to do this on the same spreadsheet so I effectively either type in the sales price or the margin % and it then works out the other. The margin box has the equation in it at the moment so if I was to type something into that it would presumably delete the equation and I suppose the same would happen if I was to enter an equation in the sale price. Maybe I need some sort of 'what if' statement in each box. If the cell is blank then resort to the equation, if not then accept the value typed in. Hope that all made sense to someone!!

 

[anandvarma]

can you provided with data and intended answers

 

[bravors]

Not sure what you mean with this. What data do you need me to send.

 

[anandvarma]

Example and answer

 

[baitmaster]

welcome to the board

easiest way is to have an additional set of calculations that do this, because each cell would ideally contain either data or a fornula, but not both at the same time. You could have it so you put your data in one cell, and the formula appears in the other, but you would need a macro to write it for you - as a rule, keep it simple though

So you could have your cost in column A, preferred margin in column B, and price in column C. Your calculations would then use whichever of the last two you want to use, using a simple IF

For example
=IF(B1="",(C1-A1)/C1,B1) is your margin (I think - my brackets are different to yours, but Im making this up as I go along so its not tested, sorry)

Based on your formula for margin, I get S = C / (1-M) as price based on margin. You could use this in more columns of formulas if needed