# if function

#### lqorri

##### New Member
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.

 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>

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hi,

Would this work for you:

Book1
ABCDEF
1ScenarioCurrent on hand inventoryLast year's inventory shipment2 year average saleskeep 5 years of inventory on hand (based on sales)Destroy the rest
2#15010500
3#220521015
4#31002100
5#40151510
6#510100020
Sheet561
Cell Formulas
RangeFormula
F2=MAX(B2+C2-E2,0)

Formula copied down.

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

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>

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

you are correct, that is my typo.

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

Book1
ABCDEF
1ScenarioCurrent on hand inventoryLast year's inventory shipment2 year average saleskeep 5 years of inventory on hand (based on sales)Destroy the rest
2#15010500
3#220521015
4#31002100
5#4015150
6#510100010
7#61001001050100
Sheet561
Cell Formulas
RangeFormula
F2=MIN(MAX(B2+C2-E2,0),B2)

Formula copied down.

You're welcome.

Replies
3
Views
145
Replies
5
Views
208
Replies
3
Views
387
Replies
0
Views
162
Replies
2
Views
79

1,218,920
Messages
6,145,227
Members
450,603
Latest member
jpbegley

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back