Formula Problems

aem

New Member
Joined
Nov 23, 2005
Messages
5
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.
 

Some videos you may like

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
Joined
Aug 31, 2005
Messages
5,828
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
Joined
Nov 23, 2005
Messages
5
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
Joined
Aug 31, 2005
Messages
5,828
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,502
Members
412,671
Latest member
xcihan
Top