Multiply cell with X until positive

[mr_strops]

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

[#2]

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.

[#3]

@mathetes 

I have attached excel with calculation. Tried several ifs, but it is not solution since MOQs (minimal order quantities) are variable volume. I want to set MOQ is B column next to Component and one row below an actual stock qty of this component.

[#4 · Accepted solution]

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

[#5]

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.