Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding to a specific value (up or down)
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding to a specific value (up or down)
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)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 "Dave" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding to a specific value (up or down)
Dave,
No, not completely clear. But I think MROUND may be what you are looking for. As in: =MROUND(yourforcastformula,A1) Tim C "Dave" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding to a specific value (up or down)
Actually, this shorter version works:
F1: =CEILING(MAX(SUM(C1:E1)-B1,0),A1) *********** Regards, Ron XL2002, WinXP-Pro "Ron Coderre" wrote: 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)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 "Dave" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlight a row if a specific cell is specific numbers/words | Excel Worksheet Functions | |||
rounding to a specific number | Excel Worksheet Functions | |||
How do I make a cell date specific to input a value on a specific. | Excel Discussion (Misc queries) | |||
specific rounding of formulas which may need to include an IF stat | Excel Worksheet Functions | |||
Hyperlink to specific sheet in Excel Web File | Links and Linking in Excel |