Forum Discussion
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 to necessary demand at the time and multiplying it with MOQ. It works well if I have large MOQs, but problem starts with small MOQ.
Meaning if required number is greater than MOQ, I got negative stock qty, but it's not acceptable, that is why I want to multiply this MOQ number with X until it leaves positive number in stock.
I want to find this X or better excel do this for me. 🙂
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.
4 Replies
- It would be a lot easier to respond if you helped your would-be helpers by posting a copy of the spreadsheet you have. That would insure that we're working with the same layout, assumptions, etc.
If your actual spreadsheet contains proprietary data, then modify it into a mockup that still represents the calculation variables accurately.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.
