Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulahelp
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulahelp
if not too many start with the largest using nested if's
=if(a2100,4,if(a280,3,"")) or use a table with the VLOOKUP function. Look in HELP index -- Don Guillett SalesAid Software "aem" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulahelp
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 aem wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|