Forum Discussion
Multiply cell with X until positive
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.
If your actual spreadsheet contains proprietary data, then modify it into a mockup that still represents the calculation variables accurately.
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.
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.
