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

[faphillips]

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

 

[Marcílio_Lobão]

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]

Cost = Sale Price/(1+ Margin)

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

 

[faphillips]

Cost = Sale Price/(1+ Margin)
Sheet2

	A	B	C
Sale Price	Margin	Cost

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:67px;"><col style="width:50px;"><col style="width:34px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]27.50[/TD]
[TD="align: right"]400%[/TD]
[TD="align: right"]5.50[/TD]

</tbody>

Spreadsheet Formulas

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]

Really apprecaited thank you.

 

[JoeMo]

Really apprecaited thank you.

You are welcome - thanks for the reply.