if function

[lqorri]

Hello- i have an excess inventory on hand which i need to figure out which part to keep and which part to destroy.

my goal is to get a formula that will give me the desired output for the column in red font (destroy the rest). I have done a few scenarios to hopefully explain this better. As you can see i am adding (current on hand + last years inventory shipment)-keep 5 years of inventory on hand.

Thank you in advance.




[TABLE="width: 830"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Scenario[/TD]
[TD]Current on hand inventory[/TD]
[TD]Last year's inventory shipment[/TD]
[TD]2 year average sales[/TD]
[TD]keep 5 years of inventory on hand (based on sales)[/TD]
[TD]Destroy the rest[/TD]
[/TR]
[TR]
[TD]#1[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]50[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]#2[/TD]
[TD]20[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]#3[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]#4[/TD]
[TD]0[/TD]
[TD]15[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]#5[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

Would this work for you:

Book1
	A	B	C	D	E	F
1	Scenario	Current on hand inventory	Last year's inventory shipment	2 year average sales	keep 5 years of inventory on hand (based on sales)	Destroy the rest
2	#1	5	0	10	50	0
3	#2	20	5	2	10	15
4	#3	10	0	2	10	0
5	#4	0	15	1	5	10
6	#5	10	10	0	0	20
Sheet561

Cell Formulas
Range	Formula
F2	=MAX(B2+C2-E2,0)

Formula copied down.

 

[DanteAmor]

Assuming that your data starts in cell A2, then in cell E2 put the following formula

=IF((A2+B2)-D2<0,0,(A2+B2)-D2)

Copy down

 

[lqorri]

Hi,

thank you both for the reply. I apologize in advance as i was not clear. The goal is, i need to figure out how much inventory to destroy from the current on hand inventory. Last year shipment is used for a forecasting standpoint. I can not physically destroy that inventory, however i need to take into account as we can still use. i have added scenario 6 to hopefully help visualize. the amount to destroy would be 100 since we already have enough inventory from last years shipment to fulfill the threshold of 5 years of inventory on hand.



[TABLE="class: cke_show_border"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Scenario[/TD]
[TD]Current on hand inventory[/TD]
[TD]Last year's inventory shipment[/TD]
[TD]2 year average sales[/TD]
[TD]keep 5 years of inventory on hand (based on sales)[/TD]
[TD]Destroy the rest[/TD]
[/TR]
[TR]
[TD]#1[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]50[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]#2[/TD]
[TD]20[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]#3[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]#4[/TD]
[TD]0[/TD]
[TD]15[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]#5[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]#6[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]10[/TD]
[TD]50[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

I'm unclear of your requirements...

According to your New description in Post # 4, then wouldn't the answer be 10 for Scenario #5 , and 0 for Secnario #4 ?

 

[lqorri]

you are correct, that is my typo.

 

[jtakw]

Can't read your latest post, but if my assumption is correct in Post # 5, then use this:

Book1
	A	B	C	D	E	F
1	Scenario	Current on hand inventory	Last year's inventory shipment	2 year average sales	keep 5 years of inventory on hand (based on sales)	Destroy the rest
2	#1	5	0	10	50	0
3	#2	20	5	2	10	15
4	#3	10	0	2	10	0
5	#4	0	15	1	5	0
6	#5	10	10	0	0	10
7	#6	100	100	10	50	100
Sheet561

Cell Formulas
Range	Formula
F2	=MIN(MAX(B2+C2-E2,0),B2)

Formula copied down.

 

[lqorri]

thank you for your help. very helpful and it worked.

 

[jtakw]

You're welcome.