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
Check out the Formula Handbook template!