Cost profit Margin Calculator Help

Fixer32

New Member
Joined
Apr 8, 2017
Messages
7
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.2575%
ItemsDescriptionCostSelling 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
Joined
Apr 8, 2017
Messages
7
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})),"")
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,103
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,086
Messages
5,622,615
Members
415,914
Latest member
Uriel Castillo

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top