Need Formula: Per Facility per Model #, how much +/- from par level.
I need to add a column formula within the Stock Inventory Sheet, in the Qty. Above/Below Par column, that calculates per Facility per Model #, take the quantity from Order Type Stock Order Row – Facility Specific Part(s), how much above or below are we from the Par Level? So something along these lines:
Per Facility, Per Model #, calculate the Order Type "Stock Order Part(s)", Qty./Used (-#) "#" - Order Type "Facility Specific Part(s), Qty./Used (-#) = # and calculate the Qty Above/Below Par from that # to the Par Level (Reorder).
Best Answer
-
I think I figured it out! The beginning of the formulas were backwards. I swapped the Par Level and the Qty Used and it worked. Thank you again!!
Answers
-
Hi @Ashley Pierce ,
Does this get you what you're looking for?
=MAX(COLLECT([par level (reorder)]:[par level (reorder)], [facility(s)]:[facility(s)], [facility(s)]@row, [model number]:[model number], [model number]@row)) - SUMIF([Qty./Used (-#)]:[Qty./Used (-#)], [facility(s)]:[facility(s)], [facility(s)]@row, [model number]:[model number], [model number]@row)
A negative result would mean you need to reorder.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Seems like it's close, but there's something still off. It's coming back with #INCORRECT ARGUMENT in column "Qty Above/Below Par". It seems to connect with everything you have listed though. Originally I thought the Order Status would need to be in the formula, but if the Qty./Used (-#) has a negative number, this automatically means it's the Stock Material Used status.
Any suggestions? I have the column type as Text/Number.
-
Okay, so somehow I fixed it to work; however, the calculation is wrong. It appears to be taking 25 - 37.5? to get the -12.5? But that's not right. How do I fix that?
-
I think I figured it out! The beginning of the formulas were backwards. I swapped the Par Level and the Qty Used and it worked. Thank you again!!
-
Excellent. Glad you found a solution. Thank you for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!