Forum Discussion

mr_strops's avatar
mr_strops
Copper Contributor
Oct 15, 2021
Solved

Multiply cell with X until positive

Hi, I am calculating necessary stock quantity to forecast next purchase order. Ther is a limitation of MOQ (minimuma order quantity) that I need to stick with. So I am comparing actual stock qty t...
excel
Formulas and Functions
  • mathetes's avatar
    mathetes
    Oct 16, 2021

    mr_strops 

     

    Try this formula.

    =IF(G$4>F9,$B8*(QUOTIENT(G$4,$B8)+1),0)

    I entered it in row 8 of your spreadsheet and it seems to work. This is basically your original IF formula with the needed multiplier added in. That multiplier of the MOQ is determined by using the QUOTIENT function to divide the current projection projections by MOQ, getting the quotient (the number without remainder) and adding 1...seems to work across that row all the way. Let me know if there's some situation it doesn't handle.

mathetes's avatar
mathetes
Gold Contributor
Oct 16, 2021

mr_strops 

 

Try this formula.

=IF(G$4>F9,$B8*(QUOTIENT(G$4,$B8)+1),0)

I entered it in row 8 of your spreadsheet and it seems to work. This is basically your original IF formula with the needed multiplier added in. That multiplier of the MOQ is determined by using the QUOTIENT function to divide the current projection projections by MOQ, getting the quotient (the number without remainder) and adding 1...seems to work across that row all the way. Let me know if there's some situation it doesn't handle.

mr_strops's avatar
mr_strops
Copper Contributor
Oct 16, 2021
Wow, it works! You just saved my week.
Thanks mathetes
I knew there should be a formula for this, just didn't know which one.

Resources