Hi
PFA Excel sheet in which how to automate Qty to Hold to compensate debit amt (outstanding) and further Release (Free) remaining Qty after recovering debit amt. under same client code. Hold Qty can not be more than given Total Quantity. regardsHi
PFA Excel sheet in which how to automate Qty to Hold to compensate debit amt (outstanding) and further Release (Free) remaining Qty after recovering debit amt. under same client code. Hold Qty can not be more than given Total Quantity. regardsI suggest this formula for your column N.
=MIN(-INT($G3/-$E3), $D3)
The calculation can't return a result that is greater than the Total Quantity. The double minus effects a round-up: For example, any value greater than 10 will be interpreted as 11.
The formula for the Free Quantity should be calculated as the difference between the Total Quantity and the Hold Quantity. I recommend the use of absolute references for all columns.
=$D3-$N3
You may like to reconsider the use of column P. Your draft sheet repeats the value from column D but arrived at by a different calculation - not commendable. Total - Hold = Free. Therefore Hold + Free = Total. Whom do you wish to confuse? I suggest this formula instead.
=IF($G3>$F3, "Overdrawn", "OK")
Since the formula in column N doesn't permit a quantity larger than is available you need a safeguard for scuh an event. The above formula will show a warning. Use conditional formatting to draw attention to it.
You claim that the formula incolumn L is working. It will show a negative amount if the Debit is greater than the Holding Value. The formula below prevents that. It can't show a value less than zero. Whether or not that is a good thing is another question but with the warning in column P you should be all right.
=MAX($F3-$G3,0)