Cost profit Margin Calculator Help

[Fixer32]

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]

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})),"")

 

[Snakehips]

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]

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%​

 

[shg]

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.