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

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

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.