Rounding numbers

[jlgray0127]

I am trying to round an expression to a multiple of another field.

Example would be... I have a column which is ([Stocked_Units]-[Demanded_Units]) = column [Variance]
I want to add a column that looks at the standard pack qty of a box, [Std_Pk_Qty] and the Variance to give me the number of boxes to put in my rack. So...

We currently keep 300 pc's on the rack, which pack 100 in a box. This would be 3 boxes. Demand is at 312 pcs, so I want the new calculation to tell me to stock 4 boxes to cover my demand.
Same thing for this

We currently keep 300 pcs on the rack, and demand is now 198. Pack qty is 100. I want it to tell me that I need to put 2 boxes on the rack now, instead of 3.



I want it to round up to the Standard Pack qty, which can be any number... It all depends on what the product is. Std pack could be 3, 17, 23, or even 4000. That is why I need it to look at the [Std_Pk_Qty] to the multiple.

Thanks in advance!

[aytee111]

Something like this?

for x = 1 to 100
if (demand / qtyperbox) < (packqty * x) Then
Boxesreqd = packqty
exit for
endif
next x

Totally untested!

[aytee111]

Oops - should be Boxesreqd = packqty * x

[orange]

This may offer another approach.

From Allen Browne's site

Rounding up

Rounding up To round upwards towards the next highest number, take advantage of the way Int() rounds negative numbers downwards, like this:
- Int( - [MyField])
As shown above, Int(-2.1) rounds down to -3. Therefore this expression rounds 2.1 up to 3.

[ssanfu]

My version:

Public Function MyRound(pDemand As Variant, pQnty As Integer) As Integer
'pDemand is Demanded Units
'pQnty is standard pack qty of a box
'
' returns the number of boxes to stock to cover demand

   MyRound = Int(pDemand / pQnty) - ((pDemand Mod pQnty) > 0)

End Function

[orange]

You could alter the MyRound line to


MyRound = -Int( -pDemand / pQnty)

but either way answers the poster's question.