if function

lqorri

New Member
Joined
Feb 13, 2017
Messages
14
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.




ScenarioCurrent on hand inventoryLast year's inventory shipment2 year average saleskeep 5 years of inventory on hand (based on sales)Destroy the rest
#15010500
#220521015
#31002100
#40151510
#510100020

<tbody>
</tbody>

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,375
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Would this work for you:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Scenario</td><td style=";">Current on hand inventory</td><td style=";">Last year's inventory shipment</td><td style=";">2 year average sales</td><td style=";">keep 5 years of inventory on hand (based on sales)</td><td style=";">Destroy the rest</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">#1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">10</td><td style="text-align: right;;">50</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">#2</td><td style="text-align: right;;">20</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">10</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">#3</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">#4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">15</td><td style="text-align: right;;">1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">#5</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">20</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet561</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=MAX(<font color="Blue">B2+C2-E2,0</font>)</td></tr></tbody></table></td></tr></table><br />

Formula copied down.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Feb 13, 2017
Messages
14
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.



BCDEF
ScenarioCurrent on hand inventoryLast year's inventory shipment2 year average saleskeep 5 years of inventory on hand (based on sales)Destroy the rest
#15010500
#220521015
#31002100
#40151510
#510100020
#61001001050100

<tbody>
</tbody>
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,375
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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 ?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,375
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Scenario</td><td style=";">Current on hand inventory</td><td style=";">Last year's inventory shipment</td><td style=";">2 year average sales</td><td style=";">keep 5 years of inventory on hand (based on sales)</td><td style=";">Destroy the rest</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">#1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">10</td><td style="text-align: right;;">50</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">#2</td><td style="text-align: right;;">20</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">10</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">#3</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">#4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">15</td><td style="text-align: right;;">1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">#5</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">#6</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">10</td><td style="text-align: right;;">50</td><td style="text-align: right;;">100</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet561</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=MIN(<font color="Blue">MAX(<font color="Red">B2+C2-E2,0</font>),B2</font>)</td></tr></tbody></table></td></tr></table><br />

Formula copied down.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,375
Office Version
  1. 2016
Platform
  1. Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,399
Members
414,063
Latest member
N_Bates

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top