# Calculate GP based on %mark up and discount

#### Topcat2311

##### New Member
Hi
This has annoying me for days hence the post - i'm almost certain the calculation can be done, but it's eluding me...

Basically I have a price list which gives me the % mark up/discount from trade on a range of products (so no fixed cost "price"). I need to create a formula in the "required discount" column that reacts to a value being placed in the "GP" column (in other words if I want to make 20% on an item it would calculate the discount from trade)

Example -

Range A has a discount from trade of 35%

Cost £162.50

Cost £227.50

Now if you wanted to sell both of these items and make 20% margin, you’d divide cost by 0.80

Item 1 - £203.12
Item 2 - £284.38

This makes the percentage discount from trade 18.75% to = 20% margin - but try as I might I can't figure out the formula to calculate this (and thus apply to other "ranges".

Can anyone out there put me out of my misery?

TC

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Welcome to MrExcel.

Excel 2010
ABCD
2250.00162.5020%18.75%
3350.00227.5020%18.75%
Sheet5
Cell Formulas
RangeFormula
D2=1-((B2/(1-C2)/A2))

 A B C D E 1 Name Trade Discount Margin %discFtrade 2 Item 1 750 35% 12% 26.14% 3 Item 2 850 35% 14% 24.42% 4 Item 3 950 35% 16% 22.62% 5 Item 4 1050 35% 18% 20.73% 6 Item 5 1150 35% 20% 18.75%

<colgroup><col><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>

Welcome to MrExcel.

Excel 2010
ABCD
2250.00162.5020%18.75%
3350.00227.5020%18.75%

</tbody>
Sheet5

Worksheet Formulas
CellFormula
D2=1-((B2/(1-C2)/A2))

</tbody>

<tbody>
</tbody>

Hi
Yes and no - in that for the formula i've been asked to create A2 & B2 have no value. In other words, the only data i've been given is the discount from trade for the range?

So for example column A would read "mark up to trade" (in this case 35%) and then i'm left with trying to calculate either C2 or D2.

Does that make sense?!

In that case it's:

=1-((1-35%)/(1-20%))

In that case it's:

=1-((1-35%)/(1-20%))

That
Is
Awesome

Thank you so much - it's been doing my head in for days!

Replies
1
Views
316
Replies
3
Views
482
Replies
4
Views
459
Replies
4
Views
1K
Replies
3
Views
489

1,219,895
Messages
6,150,830
Members
450,987
Latest member
PopeScooby

### 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