Rounding to a specific value (up or down)

[Guest]

Hi,

I'm trying to create a formula based on a minium order qty (MOQ)

for example cell a1 would have the MOQ figure, b1 would have an opening
stock figure, c1, d1, & e1 would have forecast figures.

I would like it to calculate an order figure based on the 3 forecasts, minus
the current stock holding and rounding it based on the moq

Hope thats clear !!!!!

Thanks very much in advance

Dave

 

[Guest]

Try something like this:

Where:
A1: (MOQ)
B1: (Stock on hand)
C1: (Forecast 1)
D1: (Forecast 2)
E1: (Forecast 3)
F1: =IF((SUM(C1:E1)-B1)>0,CEILING(SUM(C1:E1)-B1,A1),0)

Effectively, that formula determines if Stock-on-hand less forecast demand
is greater than zero, than reorder in MOQ increments to cover the demand.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Tim C]

Dave,

No, not completely clear. But I think MROUND may be what you are looking
for. As in:

=MROUND(yourforcastformula,A1)

Tim C

 

[Guest]

Actually, this shorter version works:

F1: =CEILING(MAX(SUM(C1:E1)-B1,0),A1)

***********
Regards,
Ron

XL2002, WinXP-Pro