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>
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,792
Office Version
  1. 2010
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
7,245
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
7,245
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:


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.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
You're welcome.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,199
Messages
5,835,933
Members
430,396
Latest member
dzifna

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