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

Options
✭✭✭✭

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
edited 04/29/21
Options

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.

• ✭✭✭✭
Options

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.

• ✭✭✭✭
Options

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?

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!