formulahelp

[Guest]

Limited excel formula knowledge. Trying to create a formula based on a
dollar amount input to give you a specific output. I'm trying to use this in
a self created cost / sell estimate form. If I input a range between $1 &
&100 in the cost cell then I want a sell price to reflect a mark up of 100%.
If the input range is between $101 & $300 then I want a sell price to reflect
a mark up of 50%. If input range is between $301 & $500 then I want a sell
price to reflect a mark up of 30%..... and so on. This is probably not too
complicated for most but like I mentioned above.....Any help is great.

 

[Don Guillett]

if not too many start with the largest using nested if's
=if(a2>100,4,if(a2>80,3,""))
or use a table with the VLOOKUP function. Look in HELP index

 

[Roger Govier]

Hi

Make yourself a Lookup table either on another part of the sheet or on
another sheet. Say in Sheet2 cells A1:B5

0 100%
100 50%
300 30%
500 25%
1000 20%

Mark this range of cells and give it a name by typing Markup into the white
pane to the left of Column A and pressing Enter.

On sheet1, assuming your input cell with the cost price to be in column C,
enter in D1
=C1*VLOOKUP(C1,MARKUP,2)
This will give the amount of the markup itself. If you wanted it to be the
final value including markup, then amend to
=C1+C1*VLOOKUP(C1,MARKUP,2)

Change ranges to suit.

Regards

Roger Govier