How to reverse a formula?

[Fred45]

I have a very useful pricing spreadsheet which uses some fairly complex formulas to calculate prices across 3 sales channels at a set profit margin, based on unit cost.

I've attached the file. On tab1 cell E2, you enter the desired profit margin. On tab2 column B:B, the unit costs are entered. Then by dragging down the next 3 columns, you are given the different prices per sales channel.

What I need (in addition to this) is to be able to calculate the profit margin by entering in selling prices and unit costs (rather than calculate the selling price by entering profit margin and unit cost). Can anyone think of a way to alter this spreadsheet to achieve this? Or would I need to start from scratch?

Would greatly appreciate any help.

Thanks.

[newdoverman]

I think that this might be what you are after. It isn't simply a matter of subtracting the unit cost from the sales price as there are costs to be subtracted. If I understand your worksheet correctly, the costs are already calculated on Tab1. I think that you can use the total costs that are calculated on row 21 of Tab1. Doing so, this is what I came up with.

Please Login or Register  to view this content.

This is using the existing data on Tab2 and calculating backwards. If you want to have this calculation elsewhere, change the C2 (Selling Price) and B2 (Unit Price) cell references to where you want to make the entries. This formula can be filled across to get the three channels and down for the different products.