Need Formula: Per Facility per Model #, how much +/- from par level.

Ashley Pierce

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).

Ashley Pierce

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!!

Mark Cronk

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

Ashley Pierce

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.

Ashley Pierce

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?

Ashley Pierce

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!!

Mark Cronk

Excellent. Glad you found a solution. Thank you for contributing to the Community.

Mark