Nested IF Function

Marco Margaritelli · 07-28-2006, 10:40 PM

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, therefore:
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!

JMB · 07-28-2006, 11:10 PM

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(B2>A2*1.5,C2*0.6,IF(B2>A2,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(B2>A2,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, therefore:
> 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!
>
>

**daddylonglegs** · 07-29-2006, 09:42 AM

=if(b2>a2*3/2,0.6,if(b2>a2,0.8,1))*c2

Response to nesting problem · 07-29-2006, 02:45 PM

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(B2>15,B2-A2))
and for cell e: =IF(B2<=15,IF((B2-A2)<1,C2,C2*0.8),IF(B2>15,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, therefore:
> 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!
>
>

JMB · 07-29-2006, 08:05 PM

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(B2>15,B2-A2))
> and for cell e: =IF(B2<=15,IF((B2-A2)<1,C2,C2*0.8),IF(B2>15,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, therefore:
> > 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!
> >
> >

JMB · 07-29-2006, 11:00 PM

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(B2>15,B2-A2))
> > and for cell e: =IF(B2<=15,IF((B2-A2)<1,C2,C2*0.8),IF(B2>15,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, therefore:
> > > 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!
> > >
> > >

Response to nesting problem · 07-31-2006, 02:50 AM

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(B2>15,B2-A2))
> > > and for cell e: =IF(B2<=15,IF((B2-A2)<1,C2,C2*0.8),IF(B2>15,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, therefore:
> > > > 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!
> > > >
> > > >

Response to nesting problem · 07-31-2006, 03:00 AM

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(B2>15,B2-A2))
> > > and for cell e: =IF(B2<=15,IF((B2-A2)<1,C2,C2*0.8),IF(B2>15,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, therefore:
> > > > 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!
> > > >
> > > >