Rounding for order size

[Guest]

I'm doing a forecast using some formulas.
I want the result to take in consideration the minimun order size.

For example if my forecast is 38 I want the result to be expressed as 48
which is the minimun package size.

The same for example if forecast is 500 I want it to show 480 or 528 which
are the actual amounts that I can request.

How can I do this?

FA

 

[Biff]

Hi!

So, what you want to do is round to the nearest multiple of 48?

=ROUND(A1/48,0)*48

Biff

 

[Guest]

Create a two-column table. In the first column, put the breakpoints (the
quantities at which you have to go to the next size). In the second, put the
size you'd have to request once reaching that breakpoint.

Ex: 0.1, 10
10.1, 20
20.1, 30, etc

Then use a vlookup to convert from the forecast to what you'd order:
=vlookup(Input_Value, a:b, 2, true). Note that the table I've shown would
always go up; you could construct it the other way, or make it three columns
and use two vlookups to return the order quantity above and below the
forecast.

 

[Ron Rosenfeld]

I'm doing a forecast using some formulas.
I want the result to take in consideration the minimun order size.

For example if my forecast is 38 I want the result to be expressed as 48
which is the minimun package size.

The same for example if forecast is 500 I want it to show 480 or 528 which
are the actual amounts that I can request.

How can I do this?

FA

Wouldn't you always want to round up?

If so, you could use =CEILING(num,48)


--ron