software

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, amp; 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

Try something like this:

Whe
A1: (MOQ)
B1: (Stock on hand)
C1: (Forecast 1)
D1: (Forecast 2)
E1: (Forecast 3)
F1: =IF((SUM(C1:E1)-B1)gt;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-Proquot;Davequot; wrote:

gt; Hi,
gt;
gt; I'm trying to create a formula based on a minium order qty (MOQ)
gt;
gt; for example cell a1 would have the MOQ figure, b1 would have an opening
gt; stock figure, c1, d1, amp; e1 would have forecast figures.
gt;
gt; I would like it to calculate an order figure based on the 3 forecasts, minus
gt; the current stock holding and rounding it based on the moq
gt;
gt; Hope thats clear !!!!!
gt;
gt; Thanks very much in advance
gt;
gt; Dave

Dave,

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

=MROUND(yourforcastformula,A1)

Tim C

quot;Davequot; gt; wrote in message
...
gt; Hi,
gt;
gt; I'm trying to create a formula based on a minium order qty (MOQ)
gt;
gt; for example cell a1 would have the MOQ figure, b1 would have an opening
gt; stock figure, c1, d1, amp; e1 would have forecast figures.
gt;
gt; I would like it to calculate an order figure based on the 3 forecasts,
gt; minus
gt; the current stock holding and rounding it based on the moq
gt;
gt; Hope thats clear !!!!!
gt;
gt; Thanks very much in advance
gt;
gt; Dave
Actually, this shorter version works:

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

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

XL2002, WinXP-Proquot;Ron Coderrequot; wrote:

gt; Try something like this:
gt;
gt; Whe
gt; A1: (MOQ)
gt; B1: (Stock on hand)
gt; C1: (Forecast 1)
gt; D1: (Forecast 2)
gt; E1: (Forecast 3)
gt; F1: =IF((SUM(C1:E1)-B1)gt;0,CEILING(SUM(C1:E1)-B1,A1),0)
gt;
gt; Effectively, that formula determines if Stock-on-hand less forecast demand
gt; is greater than zero, than reorder in MOQ increments to cover the demand.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Davequot; wrote:
gt;
gt; gt; Hi,
gt; gt;
gt; gt; I'm trying to create a formula based on a minium order qty (MOQ)
gt; gt;
gt; gt; for example cell a1 would have the MOQ figure, b1 would have an opening
gt; gt; stock figure, c1, d1, amp; e1 would have forecast figures.
gt; gt;
gt; gt; I would like it to calculate an order figure based on the 3 forecasts, minus
gt; gt; the current stock holding and rounding it based on the moq
gt; gt;
gt; gt; Hope thats clear !!!!!
gt; gt;
gt; gt; Thanks very much in advance
gt; gt;
gt; gt; Dave