Declining/Variable/Curved Mark-up

[RREML]

Hi Mr.Excel,

I'm trying to streamline my business and I'm having a heck of a time with a spreadsheet I'm working on. I'd like to create a spread sheet for my estimators that allows them to put in the quantity, description and cost of goods and my spreadsheet will determine the mark-up based on the total cost of the good provided. It will also display the GM% and GM$. I have mostly everything figured out on my own (I was even kind of proud of the GM% calculation I figured out all by myself) only to spend the past two hours trying ROUND, IF, OR, LOOKUP formulas to no avail. IF was almost successful, but it only provides two different outcomes. I have searched and have found similar questions but no answer seems to fit exactly what I need.

I'd like to run my mark-ups like this:

$0.01-$1.00 3.5
$1.01-$2.00 3.0
$2.01-$3.00 2.8
$3.01-$4.00 2.6
$4.01-$5.00 2.4
$5.01-$10.00 2.3
$10.01-$20.00 2.2
$20.01-$50.00 2.0
$50.01-$100.00 1.92
$100.01-$250.00 1.82
$250.01-$500.00 1.67
$500.01-$1000.00 1.55
$1000.01-$2500.00 1.43
>$2500.00 1.39


and create this:



A B C D E F G
QTY DESC COST (each) TOTAL Sale Price Margin% Margin$
10 Widget 1.00 10.00 23.00 57(rounded) 13


Where D E F G would be automatically calculated, I'm only struggling with the formula for E.

If anyone could help me out or give me an example of what I need to do, I'd be very grateful! Thanks!

Edit: Forgot my manners (s'been a long day)

 

[oldbrewer]

cost 1 items 10 total cost = 10

change your table to
0.01 3.5
1.00 3.0
2.00 2.8
3.00 ...........etc
4.00
5.00
10.00
20.00
50.00
100.00
250.00
500.00
1000.00
2500.00
1000000.00

call your table above mytable

total cost is in cell D2

=vlookup(D2,mytable,2) gives youthe discount factor