Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Function
Please I need some help on this IF function that should be pretty easy, but I
cannot figure it out... I want to use this function to automatically discount the price of a certain product if overstocked. Column "A" contains the Maximum Stock allowed. Anything over this Max Stock level should go "On Sale", depending on the overstock value. Greater the overstock, greater the discount. (I just need two discount level: -20% Discount for items up to 50% over the Maximun Stock Allowed, and -40% Discount for items over 50% the Maximum Stock Allowed). In my example I use a product with 10 pcs Max Stock, therefo If the product is OVER 10 but BELOW 15, will go On Sale at -20%, If the product is any value OVER 15 will go On Sale at -40%. Column "B" contains the actual inventory/units for the specific product, Column "C" contains the Regular Retail Price for the product. Column "D" is the result of the IF function: the Sale Price. This single formula should give the "On Sale" prices as shown in sample scenarios below: Scenario 1: A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 8 $100.00 $100.00 (Item is not in overstock: Sale Price stay the same like List Price) Scenario 2: (Product slightly in overstock 14 pcs) A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 14 $100.00 $80.00 (Item is overstock but not seriously being the Qty. 14 below 15 which is the Max Stock Level + 50%: Sale Price will be -20% from List Price) Scenario 3: (Product seriously in Overstock: 19 pcs) A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 19 $100.00 $60.00 (Item is seriously in Overstock being ABOVE 15 which is the Max Overstock + 50%: Sale Price will be -40% from List Price) ========================================== Thank you for helping! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Function
I think this is what you are looking for. I'm unsure what you want if the
items in stock is exactly 50% of the maximum amount. I assumed a 20% discount. =IF(B2A2*1.5,C2*0.6,IF(B2A2,C2*0.8,C2)) If you want 40% discount for items exactly 50% over the max =IF(B2=A2*1.5,C2*0.6,IF(B2A2,C2*0.8,C2)) "Marco Margaritelli" wrote: Please I need some help on this IF function that should be pretty easy, but I cannot figure it out... I want to use this function to automatically discount the price of a certain product if overstocked. Column "A" contains the Maximum Stock allowed. Anything over this Max Stock level should go "On Sale", depending on the overstock value. Greater the overstock, greater the discount. (I just need two discount level: -20% Discount for items up to 50% over the Maximun Stock Allowed, and -40% Discount for items over 50% the Maximum Stock Allowed). In my example I use a product with 10 pcs Max Stock, therefo If the product is OVER 10 but BELOW 15, will go On Sale at -20%, If the product is any value OVER 15 will go On Sale at -40%. Column "B" contains the actual inventory/units for the specific product, Column "C" contains the Regular Retail Price for the product. Column "D" is the result of the IF function: the Sale Price. This single formula should give the "On Sale" prices as shown in sample scenarios below: Scenario 1: A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 8 $100.00 $100.00 (Item is not in overstock: Sale Price stay the same like List Price) Scenario 2: (Product slightly in overstock 14 pcs) A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 14 $100.00 $80.00 (Item is overstock but not seriously being the Qty. 14 below 15 which is the Max Stock Level + 50%: Sale Price will be -20% from List Price) Scenario 3: (Product seriously in Overstock: 19 pcs) A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 19 $100.00 $60.00 (Item is seriously in Overstock being ABOVE 15 which is the Max Overstock + 50%: Sale Price will be -40% from List Price) ========================================== Thank you for helping! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Function
=if(b2a2*3/2,0.6,if(b2a2,0.8,1))*c2 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=566228 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Function
actually you could have 2 formulas that would give you a better data output
a=max items, b=in stock, c=list price, d=number of items in overstock(OS), e=OS itmes discount for cell d this is the formula: =IF(B2<=15,IF((B2-A2)<1,0,B2-A2),IF(B215,B2-A2)) and for cell e: =IF(B2<=15,IF((B2-A2)<1,C2,C2*0.8),IF(B215,C2*0.6)) this will not only give you the discounted price but also the number of items to put out at that price. Both cells will be soley dependant on cell b2. "Marco Margaritelli" wrote: Please I need some help on this IF function that should be pretty easy, but I cannot figure it out... I want to use this function to automatically discount the price of a certain product if overstocked. Column "A" contains the Maximum Stock allowed. Anything over this Max Stock level should go "On Sale", depending on the overstock value. Greater the overstock, greater the discount. (I just need two discount level: -20% Discount for items up to 50% over the Maximun Stock Allowed, and -40% Discount for items over 50% the Maximum Stock Allowed). In my example I use a product with 10 pcs Max Stock, therefo If the product is OVER 10 but BELOW 15, will go On Sale at -20%, If the product is any value OVER 15 will go On Sale at -40%. Column "B" contains the actual inventory/units for the specific product, Column "C" contains the Regular Retail Price for the product. Column "D" is the result of the IF function: the Sale Price. This single formula should give the "On Sale" prices as shown in sample scenarios below: Scenario 1: A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 8 $100.00 $100.00 (Item is not in overstock: Sale Price stay the same like List Price) Scenario 2: (Product slightly in overstock 14 pcs) A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 14 $100.00 $80.00 (Item is overstock but not seriously being the Qty. 14 below 15 which is the Max Stock Level + 50%: Sale Price will be -20% from List Price) Scenario 3: (Product seriously in Overstock: 19 pcs) A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 19 $100.00 $60.00 (Item is seriously in Overstock being ABOVE 15 which is the Max Overstock + 50%: Sale Price will be -40% from List Price) ========================================== Thank you for helping! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Function
15 items was only applicable to the example given. From the original post,
the items s/b discounted 20% when over the max level and 40% when quantity exceeds 50% of the max level. (Item is overstock but not seriously being the Qty. 14 below 15 which is the Max Stock Level + 50%: Sale Price will be -20% from List Price) The amount of overstock can be shortened to =MIN(0, B2-A2) "Response to nesting problem" wrote: actually you could have 2 formulas that would give you a better data output a=max items, b=in stock, c=list price, d=number of items in overstock(OS), e=OS itmes discount for cell d this is the formula: =IF(B2<=15,IF((B2-A2)<1,0,B2-A2),IF(B215,B2-A2)) and for cell e: =IF(B2<=15,IF((B2-A2)<1,C2,C2*0.8),IF(B215,C2*0.6)) this will not only give you the discounted price but also the number of items to put out at that price. Both cells will be soley dependant on cell b2. "Marco Margaritelli" wrote: Please I need some help on this IF function that should be pretty easy, but I cannot figure it out... I want to use this function to automatically discount the price of a certain product if overstocked. Column "A" contains the Maximum Stock allowed. Anything over this Max Stock level should go "On Sale", depending on the overstock value. Greater the overstock, greater the discount. (I just need two discount level: -20% Discount for items up to 50% over the Maximun Stock Allowed, and -40% Discount for items over 50% the Maximum Stock Allowed). In my example I use a product with 10 pcs Max Stock, therefo If the product is OVER 10 but BELOW 15, will go On Sale at -20%, If the product is any value OVER 15 will go On Sale at -40%. Column "B" contains the actual inventory/units for the specific product, Column "C" contains the Regular Retail Price for the product. Column "D" is the result of the IF function: the Sale Price. This single formula should give the "On Sale" prices as shown in sample scenarios below: Scenario 1: A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 8 $100.00 $100.00 (Item is not in overstock: Sale Price stay the same like List Price) Scenario 2: (Product slightly in overstock 14 pcs) A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 14 $100.00 $80.00 (Item is overstock but not seriously being the Qty. 14 below 15 which is the Max Stock Level + 50%: Sale Price will be -20% from List Price) Scenario 3: (Product seriously in Overstock: 19 pcs) A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 19 $100.00 $60.00 (Item is seriously in Overstock being ABOVE 15 which is the Max Overstock + 50%: Sale Price will be -40% from List Price) ========================================== Thank you for helping! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Function
Correction
=MAX(0, B2-A2) "JMB" wrote: 15 items was only applicable to the example given. From the original post, the items s/b discounted 20% when over the max level and 40% when quantity exceeds 50% of the max level. (Item is overstock but not seriously being the Qty. 14 below 15 which is the Max Stock Level + 50%: Sale Price will be -20% from List Price) The amount of overstock can be shortened to =MIN(0, B2-A2) "Response to nesting problem" wrote: actually you could have 2 formulas that would give you a better data output a=max items, b=in stock, c=list price, d=number of items in overstock(OS), e=OS itmes discount for cell d this is the formula: =IF(B2<=15,IF((B2-A2)<1,0,B2-A2),IF(B215,B2-A2)) and for cell e: =IF(B2<=15,IF((B2-A2)<1,C2,C2*0.8),IF(B215,C2*0.6)) this will not only give you the discounted price but also the number of items to put out at that price. Both cells will be soley dependant on cell b2. "Marco Margaritelli" wrote: Please I need some help on this IF function that should be pretty easy, but I cannot figure it out... I want to use this function to automatically discount the price of a certain product if overstocked. Column "A" contains the Maximum Stock allowed. Anything over this Max Stock level should go "On Sale", depending on the overstock value. Greater the overstock, greater the discount. (I just need two discount level: -20% Discount for items up to 50% over the Maximun Stock Allowed, and -40% Discount for items over 50% the Maximum Stock Allowed). In my example I use a product with 10 pcs Max Stock, therefo If the product is OVER 10 but BELOW 15, will go On Sale at -20%, If the product is any value OVER 15 will go On Sale at -40%. Column "B" contains the actual inventory/units for the specific product, Column "C" contains the Regular Retail Price for the product. Column "D" is the result of the IF function: the Sale Price. This single formula should give the "On Sale" prices as shown in sample scenarios below: Scenario 1: A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 8 $100.00 $100.00 (Item is not in overstock: Sale Price stay the same like List Price) Scenario 2: (Product slightly in overstock 14 pcs) A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 14 $100.00 $80.00 (Item is overstock but not seriously being the Qty. 14 below 15 which is the Max Stock Level + 50%: Sale Price will be -20% from List Price) Scenario 3: (Product seriously in Overstock: 19 pcs) A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 19 $100.00 $60.00 (Item is seriously in Overstock being ABOVE 15 which is the Max Overstock + 50%: Sale Price will be -40% from List Price) ========================================== Thank you for helping! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Function
Then the previous formula that I posted could be corrected as such:
cell d: =IF(B2<=(a2*1.5),IF((B2-A2)<1,0,B2-A2),IF(B2(a2*1.5),B2-A2)) cell e: =IF(B2<=(a2*1.5),IF((B2-A2)<1,C2,C2*0.8),IF(B2(a2*1.5),C2*0.6)) "JMB" wrote: Correction =MAX(0, B2-A2) "JMB" wrote: 15 items was only applicable to the example given. From the original post, the items s/b discounted 20% when over the max level and 40% when quantity exceeds 50% of the max level. (Item is overstock but not seriously being the Qty. 14 below 15 which is the Max Stock Level + 50%: Sale Price will be -20% from List Price) The amount of overstock can be shortened to =MIN(0, B2-A2) "Response to nesting problem" wrote: actually you could have 2 formulas that would give you a better data output a=max items, b=in stock, c=list price, d=number of items in overstock(OS), e=OS itmes discount for cell d this is the formula: =IF(B2<=15,IF((B2-A2)<1,0,B2-A2),IF(B215,B2-A2)) and for cell e: =IF(B2<=15,IF((B2-A2)<1,C2,C2*0.8),IF(B215,C2*0.6)) this will not only give you the discounted price but also the number of items to put out at that price. Both cells will be soley dependant on cell b2. "Marco Margaritelli" wrote: Please I need some help on this IF function that should be pretty easy, but I cannot figure it out... I want to use this function to automatically discount the price of a certain product if overstocked. Column "A" contains the Maximum Stock allowed. Anything over this Max Stock level should go "On Sale", depending on the overstock value. Greater the overstock, greater the discount. (I just need two discount level: -20% Discount for items up to 50% over the Maximun Stock Allowed, and -40% Discount for items over 50% the Maximum Stock Allowed). In my example I use a product with 10 pcs Max Stock, therefo If the product is OVER 10 but BELOW 15, will go On Sale at -20%, If the product is any value OVER 15 will go On Sale at -40%. Column "B" contains the actual inventory/units for the specific product, Column "C" contains the Regular Retail Price for the product. Column "D" is the result of the IF function: the Sale Price. This single formula should give the "On Sale" prices as shown in sample scenarios below: Scenario 1: A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 8 $100.00 $100.00 (Item is not in overstock: Sale Price stay the same like List Price) Scenario 2: (Product slightly in overstock 14 pcs) A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 14 $100.00 $80.00 (Item is overstock but not seriously being the Qty. 14 below 15 which is the Max Stock Level + 50%: Sale Price will be -20% from List Price) Scenario 3: (Product seriously in Overstock: 19 pcs) A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 19 $100.00 $60.00 (Item is seriously in Overstock being ABOVE 15 which is the Max Overstock + 50%: Sale Price will be -40% from List Price) ========================================== Thank you for helping! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Function
jmb I see what you were saying about the max formula and you are right to
shorten it down to that. "JMB" wrote: Correction =MAX(0, B2-A2) "JMB" wrote: 15 items was only applicable to the example given. From the original post, the items s/b discounted 20% when over the max level and 40% when quantity exceeds 50% of the max level. (Item is overstock but not seriously being the Qty. 14 below 15 which is the Max Stock Level + 50%: Sale Price will be -20% from List Price) The amount of overstock can be shortened to =MIN(0, B2-A2) "Response to nesting problem" wrote: actually you could have 2 formulas that would give you a better data output a=max items, b=in stock, c=list price, d=number of items in overstock(OS), e=OS itmes discount for cell d this is the formula: =IF(B2<=15,IF((B2-A2)<1,0,B2-A2),IF(B215,B2-A2)) and for cell e: =IF(B2<=15,IF((B2-A2)<1,C2,C2*0.8),IF(B215,C2*0.6)) this will not only give you the discounted price but also the number of items to put out at that price. Both cells will be soley dependant on cell b2. "Marco Margaritelli" wrote: Please I need some help on this IF function that should be pretty easy, but I cannot figure it out... I want to use this function to automatically discount the price of a certain product if overstocked. Column "A" contains the Maximum Stock allowed. Anything over this Max Stock level should go "On Sale", depending on the overstock value. Greater the overstock, greater the discount. (I just need two discount level: -20% Discount for items up to 50% over the Maximun Stock Allowed, and -40% Discount for items over 50% the Maximum Stock Allowed). In my example I use a product with 10 pcs Max Stock, therefo If the product is OVER 10 but BELOW 15, will go On Sale at -20%, If the product is any value OVER 15 will go On Sale at -40%. Column "B" contains the actual inventory/units for the specific product, Column "C" contains the Regular Retail Price for the product. Column "D" is the result of the IF function: the Sale Price. This single formula should give the "On Sale" prices as shown in sample scenarios below: Scenario 1: A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 8 $100.00 $100.00 (Item is not in overstock: Sale Price stay the same like List Price) Scenario 2: (Product slightly in overstock 14 pcs) A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 14 $100.00 $80.00 (Item is overstock but not seriously being the Qty. 14 below 15 which is the Max Stock Level + 50%: Sale Price will be -20% from List Price) Scenario 3: (Product seriously in Overstock: 19 pcs) A (Max Stock) B (Actual Inventory) C (List Price) D (Sale Price) 10 19 $100.00 $60.00 (Item is seriously in Overstock being ABOVE 15 which is the Max Overstock + 50%: Sale Price will be -40% from List Price) ========================================== Thank you for helping! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Nested "If" Function | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Countif Function -Nested | Excel Discussion (Misc queries) | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |