Round to nearest 48 unit

F

FA

I am doing a forecast of products.
Each product have a package size. Some 24 units, some 48, etc.
I want the rounding to indicate the nearest order size. For example: When
the package size is 48 and if my forecast is 40 I want the number to be 48.
If the forecast is 100, I want the number to be 96.
In excel I would use Mround.
How can I do this?

Thank you very much!
FA
 
O

Ofer

Is there a rule to the fixed amount of units you want to round to?
The only thing I can think of is using the switch

=Switch(FieldName < 36 , 24 , FieldName < 72 , 48 , FieldName < 96 , 96)
 
F

FA

I have a table of package size by item to use for the rounding. Some
products sell 48 units. Some 3,000.

I think the swith will be impossible.

Any other suggestions?

Thanks,

FA
 
T

Thomas Winkler

Hi,
I am doing a forecast of products.
Each product have a package size. Some 24 units, some 48, etc.
I want the rounding to indicate the nearest order size. For example: When
the package size is 48 and if my forecast is 40 I want the number to be 48.
If the forecast is 100, I want the number to be 96.
Click to expand...

Round(forecast / 24; 0) 24

HTH

Thomas
 
D

Daniel Lesenne

Thomas Winkler said:
Hi,


Round(forecast / 24; 0) 24
Click to expand...

If you keep the value of items per package (eg UPP) in the products table
you can replace the 24 with whatever figure fitting the particular product
Round(forecast / UPP; 0)*UPP
 
P

Peter Danes

You can add a reference to Excel (Tools / References in the VBA window) and
all of Excel's functions will be available to you. That may come in handy if
you have other mathematical issues in your DB.

--
Pete

This e-mail address is fake to keep spammers and their auto-harvesters out
of my hair. If you need to get in touch personally, I am 'pdanes' and I use
Yahoo mail. But please use the newsgroups whenever possible, so that all may
benefit from the exchange of ideas.
 
Top