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

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 04/29/21

    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.

  • Ashley Pierce
    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
    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
    Ashley Pierce ✭✭✭✭
    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!!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    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!