# Formula Problems

#### aem

##### New Member
Newbee to Excel needs direction on how to create a formula to incorporate into a business estimate sheet. I have a cost and sell columns and wish to have the sell columns reflect a certain mark-up depending on the cost dollar amount. IE: if cost is under 100.00, then mark-up cost 100%. If cost is over 100.00 but less than 300.00, then mark up cost 50%. If cost is over 301 but less than 500, then mark up cost to 30%.
Thanks for any help.

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### NBVC

##### Well-known Member
Try this, where A1 contains your Cost. I have assumed >500 is 0% markup.

=VLOOKUP(A1,{0,1;101,0.5;301,0.3;501,0},2,1)

Formatted as %

#### aem

##### New Member
Thanks for the help and formula. Unfortunately, I don't believe I explained my problem correctly. Maybe you could help me if I try again...
Cost Sell
A1 B1

I directly put a dollar amount in A1 and based on that dollar amount, I want B1 to reflect a sell price. That sell price in B1 needs to be based on dollar amount ranges I input into A1. For example:
A1 is 1 to 100.00, then B1 would reflect a sell price of 2 to 200.00
A1 is 101.00 to 300, then B2 would reflect a sell price of 151.50 to 450.00
A1 is 301.00 to 500.00, then B2 would reflect a sell price of 412.40 to 700.00.
A1 is 501.00 to infinity, then B2 would reflect a sell price of 651.30 to ........ Hope this is a better clarification.

#### NBVC

##### Well-known Member
Note: The values you gave this time aren't all obtainable with the numbers you gave originally.

i.e. over \$301 to get 412.4 is a 37% m/u and \$500 to \$700 is a 40% m/u.

Please check your numbers and adjust in the formula below. The numbers after each semicolon ( represents the percent markup (in decimal numbers). The number before the semicolon represents the lower limit for which to begin applying the adjacent percentage.

Try this in B1.

=A1+A1*VLOOKUP(A1,{0,1;101,0.5;301,0.4;501,0.3},2,1)

#### aem

##### New Member
Thanks. You solved my problem.

Replies
4
Views
71
Replies
1
Views
306
Replies
8
Views
492
Replies
1
Views
250
Replies
3
Views
104