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.

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 #1 5 0 10 50 0 #2 20 5 2 10 15 #3 10 0 2 10 0 #4 0 15 1 5 10 #5 10 10 0 0 20 <tbody> </tbody>

<tbody>
</tbody>

 

[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.

	B	C	D	E	F
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
#1	5	0	10	50	0
#2	20	5	2	10	15
#3	10	0	2	10	0
#4	0	15	1	5	10
#5	10	10	0	0	20
#6	100	100	10	50	100

<tbody>
</tbody>

 

[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.