# Markup and Margin Calculation

#### amatthews

##### New Member
I am trying to setup a spreadsheet that will help me see what my selling price should be based off of my cost, markup percent, and gross margin percent. Here is what I have setup on my spreadsheet now:

Cell Reference:
A1, is my wholesale cost
B1, is my markup percent
C1, selling price (this cell should auto populate)
D1, gross margin in dollars (this cell should auto populate)
E1, gross margin in percent

Basically, I want my Selling Price (in cell C1) and my Gross Margin in Dollars (in cell D1) to dynamically change.

What I want to do is to be able to enter in my cost and enter in assumptions for either my Markup Percent or my Gross Margin Percent. If I enter in an assumption for Gross Margin Percent then I would like to automatically see what my Markup Percent would be and visa versa. By entering in these two pieces of information (my cost and either my Markup Percent or my Gross Margin Percent) I would like to see what my selling price should be and what my gross margin would be in dollars.

I found exactly what I am trying to do on the following web site but it does not show me the formulas so that I can do this on my excel spreadsheet. The web site is: (note, you will need Java to view this)

http://www.dinkytown.net/java/ProfitMargin.html

If there is anyone out there that would be willing to help me, it would be so much appreciated and I thank you in advance!

Andy

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Andrew Poulsom

##### MrExcel MVP
You can't do that with formulas in Excel. A cell must either contain a constant or a formula. If you enter your wholesale price and markup %, then:

C1 =A1*(1+B1)
D1 =C1-A1
E1 =D1/C1

If you enter your wholesale price and gross margin %, then:

B1 =((A1*(1/(1-E1)))/A1)-1
C1 =A1*(1+B1)
D1 =C1-A1

#### amatthews

##### New Member
Thank You!

Andrew, thank you very much for your help on this!! What you provided me was exactly what I needed and my spreadsheet works now thanks to you!!

Andy

#### jwhite70

##### New Member
I keep getting an error message for the B1 formula when I enter it into the cell??

You can't do that with formulas in Excel. A cell must either contain a constant or a formula. If you enter your wholesale price and markup %, then:

C1 =A1*(1+B1)
D1 =C1-A1
E1 =D1/C1

If you enter your wholesale price and gross margin %, then:

B1 =((A1*(1/(1-E1)))/A1)-1
C1 =A1*(1+B1)
D1 =C1-A1

#### Festus Hagen

##### New Member
I keep getting an error message for the B1 formula when I enter it into the cell??

I believe you need to manually populate cells A1 and B1 for the this option:
C1 =A1*(1+B1)
D1 =C1-A1
E1 =D1/C1
and cells A1 and E1 for this option:
B1 =((A1*(1/(1-E1)))/A1)-1
C1 =A1*(1+B1)
D1 =C1-A1
-Enjoy
fh : )_~

#### jwhite70

##### New Member
Works great. Thx for your help!!

Replies
8
Views
355
Replies
2
Views
2K
Replies
15
Views
274
Replies
0
Views
61
Replies
8
Views
120

### Forum statistics

1,141,154
Messages
5,704,612
Members
421,359
Latest member
Edwardvanschothorst

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