# Cost profit Margin Calculator Help

#### Fixer32

##### New Member
I have made a calculator to calculate selling price from cost but I need help to make it tiered so the things I buy for a smaller price arer calculated higher than the more expensive ones.
I am not a big wiz kid In excel but know some basic stuff this is what i have made so far....

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
 Parts Gross Profit Margin Calculator Only "Blue" cells can be modified. Profit Margin = 0.25 75% Items Description Cost Selling Price Item 1 \$100.00 \$ 133.33 Item 2 \$ - Item 3 \$ - Item 4 \$ - Item 5 \$ - Item 6 \$ - Item 7 \$ - Item 8 \$ - Item 9 \$ - Item 10 \$ - Item 11 \$ - Item 12 \$ - Item 13 \$ - Item 14 \$ - Item 15 \$ - Item 16 \$ - Item 17 \$ - Item 18 \$ - Item 19 \$ - Item 20 \$ -

<colgroup><col style="width: 60px"><col width="133"><col width="24"><col width="72"><col width="15"><col width="83"></colgroup><tbody>
</tbody>

Cell E5 Has the formula =1-\$D\$5 but I need it to change depending on the cost like this.
I want to have this formula run if I put cost into the "blue" field E4.
1. \$.01 to \$1.00 75% GP and 300% markup
2. \$1.01 to \$5.00 71% GP and 245% markup
3. \$5.01 to \$12.00 65% GP and 185% markup
4. \$12.01 to \$26.00 60% GP and 150% markup
5. \$26.01 to \$38.00 55% GP and 124% markup
6. \$38.01 to \$50.00 50% GP and 100% markup
7. \$50.01 to \$62.00 45% GP and 82% markup
8. \$.6201 to \$74.00 40% GP and 67% markup
9. \$74.01 to \$86.00 35% GP and 55% markup
10. \$86.01 and up 30% GP and 43% markup
11. \$.01 to \$1.00 75% GP and 300% markup

Any help with this would be greatly appreciated

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### James006

##### Well-known Member
Hi,

Your Selling price is equal to Cost divided by (1 - markup )

Hope this will help

#### Fixer32

##### New Member
I guess what I need is a formula that calculates depending on what the input is for the cost depending on what it costs the margin will change automatically in the background. There would be no user input other than description and cost. the calculations would be hidden so only I can change them. So the blue cell would go away for the user input.

#### Snakehips

##### Well-known Member
Fixer32,

You could have a couple of lookup tables somewhere so that you can easily edit values if required or you can incorporate directly in your formula.
Not sure how you are wishing to calculate with both markup and Gp percentages?

Below shows a formula that is just adding a gross profit.
Sheet5

 * A B C D E F 6 Items Description * Cost * Selling Price 7 Item 1 * * £34.00 * £52.70 8 Item 2 * * £1.00 * £1.75 9 Item 3 * * £90.00 * £117.00 10 Item 4 * * £39.00 * £58.50 11 Item 5 * * * * * 12 Item 6 * * * * *

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:103px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:86px;"></colgroup><tbody>
</tbody>

 Cell Formula F7 =IFERROR(D7*(1+LOOKUP(D7,{0.01,1.01,5.01,12.01,26.01,38.01,50.01,62.01,74.01,86.01},{0.75,0.71,0.65,0.6,0.55,50,0.45,0.4,0.35,0.3})),"")

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hope that helps.

Last edited:

#### Fixer32

##### New Member

So would I just carry that same formula to all the cells in column"D", but change D7 to E7 E8 E9 and so forth till I get to D12?

#### Fixer32

##### New Member
Or should I say cells F7 to F26

#### Snakehips

##### Well-known Member

Yes, copy my formula into F7 and then drag it down as far as you need.

#### Fixer32

##### New Member
If I input \$100.00 in cell D7 I should get a selling price of \$143.86. With your formula I get \$130.00. I want to use profit margin not markup so 30% profit margin uses a multiplayer of 1.43 am I correct. I hope I'm explaining it correctly%(&%&(%%

#### Snakehips

##### Well-known Member
Then change F7 to....
=IFERROR(D7/(1-LOOKUP(D7,{0.01,1.01,5.01,12.01,26.01,38.01,50.01,62.01,74.01,86.01},{0.75,0.71,0.65,0.6,0.55,0.5,0.45,0.4,0.35,0.3})),"")

#### Fixer32

##### New Member
I think it has to be like this, your thoughts...

=
IFERROR(D7*(1+LOOKUP(D7,{0.01,1.01,5.01,12.01,26.01,38.01,50.01,62.01,74.01,86.01},{3,2.45,1.85,1.5,1.24,1,0.82,0.67,0.55,0.43})),"")

Replies
2
Views
869
Replies
1
Views
399
Replies
1
Views
297
Replies
5
Views
471
Replies
1
Views
521

1,130,292
Messages
5,641,385
Members
417,207
Latest member
Vxhaet

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