# 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

#### Fixer32

##### New Member
Sorry I slipped up like this?

IFERROR(D7/(1-LOOKUP(D9,{0.01,1.01,5.01,12.01,26.01,38.01,50.01,62.01,74.01,86.01},{3.00,2.45,1.85,1.50,1.24,1.00,0.82,0.67,.55,0.43})),"")

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Snakehips

##### Well-known Member
That's fine. I was not sure exactly how you were extending the cost price.

The main thing is that you now understand how you can lookup the applicable %.

#### shg

##### MrExcel MVP
Similar:

 A​ B​ C​ D​ E​ 1​ Cost​ Price​ GM​ ​ ​ 2​ \$ 0.01​ \$ 0.04​ 75%​ B2: =A2/LOOKUP(A2, {0;1;5;12;38;50;62;74;86}+1%, 1-{75;71;65;60;55;50;45;40;35}%) 3​ \$ 1.00​ \$ 4.00​ 75%​ C2: =1-A2/B2 4​ \$ 1.01​ \$ 3.48​ 71%​ 5​ \$ 12.00​ \$ 34.29​ 65%​ 6​ \$ 12.01​ \$ 30.03​ 60%​ 7​ \$ 38.00​ \$ 95.00​ 60%​ 8​ \$ 38.01​ \$ 84.47​ 55%​ 9​ \$ 50.00​ \$ 111.11​ 55%​ 10​ \$ 50.01​ \$ 100.02​ 50%​ 11​ \$ 62.00​ \$ 124.00​ 50%​ 12​ \$ 62.01​ \$ 112.75​ 45%​ 13​ \$ 74.00​ \$ 134.55​ 45%​ 14​ \$ 74.01​ \$ 123.35​ 40%​ 15​ \$ 86.00​ \$ 143.33​ 40%​ 16​ \$ 86.01​ \$ 132.32​ 35%​ 17​ \$ 100.00​ \$ 153.85​ 35%​

Last edited:

#### shg

##### MrExcel MVP
A piecewise-linear function might be a better choice, to avoid the discontinuities in pricing; something that costs \$86.01 has a price \$10 lower than something that costs \$86.00.

Replies
2
Views
645
Replies
1
Views
386
Replies
1
Views
289
Replies
5
Views
468
Replies
1
Views
316

1,126,992
Messages
5,622,033
Members
415,874
Latest member
JockPC

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