# Find Cost Price From Sale Price and Margin where margin is greater than 100%

#### faphillips

##### New Member
Hi

I am trying to find a formula to calculate the cost of an item from the selling price and the % margin. For example:

I buy a bottle of wine for £5.50 and sell it in my wine bar for £27.50 which I have calculated as a 400% markup where Price = A1 and Markup =B1 the formula is =A1+(A1*B1)
I now need to find a formula that will find the cost price i.e. £5.50 but only knowing that the sale price was £27.50 and the margin was 400%. It is likely that the margin will always be greater than 100%

I have managed to do this in 2 stages by converting the % figure to a whole number i.e. 400% becomes 4 and adding 1, which equals 5 and then dividing the selling price by 5. There must however be a simpler way of doing this in a formula. Any help would be very much appreciated.

Thank you

Francis

Last edited:

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Marcílio_Lobão

##### Well-known Member
Francis, Good evening.

Scenario:

A1 = 5,5
B1 = 400%
C1 = A1+(A1*B1) --> 27,5

Try to use:

D1=(C1/(B1+100%))
D1 --> 5,5

Is that what you want?
I hope it helps.

#### JoeMo

##### MrExcel MVP
Cost = Sale Price/(1+ Margin)
Excel Workbook
ABC
1Sale PriceMarginCost
227.50400%5.50
Sheet2

#### faphillips

##### New Member
Cost = Sale Price/(1+ Margin)
Sheet2

 A B C 1 Sale Price Margin Cost 2 27.50 400% 5.50

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:67px;"><col style="width:50px;"><col style="width:34px;"></colgroup><tbody>
</tbody>

 Cell Formula C2 =A2/(1+B2)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

JoeMo thank you so much simple but perfect. Appreciated

#### faphillips

##### New Member
Really apprecaited thank you.

#### JoeMo

##### MrExcel MVP
Really apprecaited thank you.
You are welcome - thanks for the reply.

Replies
2
Views
869
Replies
1
Views
399
Replies
3
Views
7K
Replies
1
Views
521
Replies
15
Views
2K

1,130,316
Messages
5,641,484
Members
417,211
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back