Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forecast a date from data given
Hello, any help with this would be more than appreciated.
I need my spreadsheet to give me a date when I will run out of parts. A2: todays date C4 - J4 has my weekly requirements L4- has my parts on hand I need to find out based on my weekly requirements when I will be out of parts. Looks something like this. A2 (today date) c4 d4 e4 f4 g4 h4 i4 j 4 L4 m4 wk1 wk2 wk3 wk4 wk5 wk6 wk7 wk8 20 20 20 20 20 20 20 20 73 (Need parts date) Any help with a formula to calculate this would, again, be more than appreciated. I've been racking my brain for a week on this one. Thanks, James |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forecast a date from data given
Bit of line wrap on the posting. Do you mean that you want to know in
M4 the projected date when you will have used up all your parts in hand (L4)? Does week 1 relate to today's date in A2? Pete On Mar 31, 6:48*pm, Jelewis72076 wrote: Hello, any help with this would be more than appreciated. I need my spreadsheet to give me a date when I will run out of parts. A2: todays date C4 - J4 has my weekly requirements L4- has my parts on hand I need to find out based on my weekly requirements when I will be out of parts. Looks something like this. A2 * * * * * (today date) * * * * * * * c4 * *d4 * *e4 * *f4 * *g4 * *h4 * *i4 *j * 4 * * * * *L4 * * * * *m4 * * * * * * *wk1 *wk2 *wk3 *wk4 wk5 *wk6 *wk7 wk8 * * * * * * * * * 20 * *20 * *20 * *20 * *20 * 20 * *20 * *20 * * * * 73 * * * * (Need parts date) Any help with a formula to calculate this would, again, be more than appreciated. *I've been racking my brain for a week on this one. Thanks, James |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forecast a date from data given
Your example shows the same weekly usage throughout. Assuming this to
be the case, put this formula in M4: =A2+INT(L4/C4*5) Format as a date. This assumes a 5-day week - change to 7 if you wish. Hope this helps. Pete On Mar 31, 6:48*pm, Jelewis72076 wrote: Hello, any help with this would be more than appreciated. I need my spreadsheet to give me a date when I will run out of parts. A2: todays date C4 - J4 has my weekly requirements L4- has my parts on hand I need to find out based on my weekly requirements when I will be out of parts. Looks something like this. A2 * * * * * (today date) * * * * * * * c4 * *d4 * *e4 * *f4 * *g4 * *h4 * *i4 *j * 4 * * * * *L4 * * * * *m4 * * * * * * *wk1 *wk2 *wk3 *wk4 wk5 *wk6 *wk7 wk8 * * * * * * * * * 20 * *20 * *20 * *20 * *20 * 20 * *20 * *20 * * * * 73 * * * * (Need parts date) Any help with a formula to calculate this would, again, be more than appreciated. *I've been racking my brain for a week on this one. Thanks, James |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forecast a date from data given
Thanks Pete,
I think you are on the right track. I tried tweaking that a little bit but not getting exactly what I need. My example did show the same number but they are actually different. Here is an actual line copy of my header and one of my part lines. Weekly Requirements 1-Apr-08 8-Apr-08 15-Apr-08 22-Apr-08 29-Apr-08 6-May-08 13-May-08 20-M 223 550 715 715 715 605 770 495 This is in C4-J4. In L4 (not shown) is my parts on hand. In M4 I would like the actual date or week (from those above) I run out of parts. In this example I have 1093 parts on hand. Therefore I would run out of parts in the 15-apr-08 week. Thus I must make more during the 8-apr-08 week. I know that because I'm giving weekly requirements instead of daily requirements it makes it difficult to pinpoint a date but I am thinking that by referencing todays date in cell A2 it will ballpark it for me. any suggestions on how to make this work will help. Thanks James "Pete_UK" wrote: Your example shows the same weekly usage throughout. Assuming this to be the case, put this formula in M4: =A2+INT(L4/C4*5) Format as a date. This assumes a 5-day week - change to 7 if you wish. Hope this helps. Pete On Mar 31, 6:48 pm, Jelewis72076 wrote: Hello, any help with this would be more than appreciated. I need my spreadsheet to give me a date when I will run out of parts. A2: todays date C4 - J4 has my weekly requirements L4- has my parts on hand I need to find out based on my weekly requirements when I will be out of parts. Looks something like this. A2 (today date) c4 d4 e4 f4 g4 h4 i4 j 4 L4 m4 wk1 wk2 wk3 wk4 wk5 wk6 wk7 wk8 20 20 20 20 20 20 20 20 73 (Need parts date) Any help with a formula to calculate this would, again, be more than appreciated. I've been racking my brain for a week on this one. Thanks, James |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forecast a date from data given
Not sure if you are still monitoring this, but you could set up a user-
defined function to do this. Essentially, add the weekly usage each week until it exceeds the amount in hand, and thus identify the number of complete weeks and the number of days by simple interpolation between the two final amounts in that sequence. Hope this helps. Pete On Apr 1, 2:42*pm, Jelewis72076 wrote: Thanks Pete, I think you are on the right track. *I tried tweaking that a little bit but not getting exactly what I need. *My example did show the same number but they are actually different. *Here is an actual line copy of my header and one of my part lines. Weekly Requirements * * * * * * * * * * * * * * * * * * * * * * * * * * 1-Apr-08 * * * *8-Apr-08 * * * *15-Apr-08 * * * 22-Apr-08 * * * 29-Apr-08 * * * 6-May-08 * * * *13-May-08 * * * 20-M 223 * * 550 * * 715 * * 715 * * 715 * * 605 * * 770 * * 495 This is in C4-J4. *In L4 (not shown) is my parts on hand. *In M4 *I would like the actual date or week (from those above) I run out of parts. *In this example I have 1093 parts on hand. *Therefore I would run out of parts in the 15-apr-08 week. *Thus I must make more during the 8-apr-08 week. *I know that because I'm giving weekly requirements instead of daily requirements it makes it difficult to pinpoint a date but I am thinking that by referencing todays date in cell A2 it will ballpark it for me. any suggestions on how to make this work will help. *Thanks James "Pete_UK" wrote: Your example shows the same weekly usage throughout. Assuming this to be the case, put this formula in M4: =A2+INT(L4/C4*5) Format as a date. This assumes a 5-day week - change to 7 if you wish. Hope this helps. Pete On Mar 31, 6:48 pm, Jelewis72076 wrote: Hello, any help with this would be more than appreciated. I need my spreadsheet to give me a date when I will run out of parts. A2: todays date C4 - J4 has my weekly requirements L4- has my parts on hand I need to find out based on my weekly requirements when I will be out of parts. Looks something like this. A2 * * * * * (today date) * * * * * * * c4 * *d4 * *e4 * *f4 * *g4 * *h4 * *i4 *j * 4 * * * * *L4 * * * * *m4 * * * * * * *wk1 *wk2 *wk3 *wk4 wk5 *wk6 *wk7 wk8 * * * * * * * * * 20 * *20 * *20 * *20 * *20 * 20 * *20 * *20 * * * * 73 * * * * (Need parts date) Any help with a formula to calculate this would, again, be more than appreciated. *I've been racking my brain for a week on this one. Thanks, James- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I fix a forecast date that has passed for a zero value? | Excel Discussion (Misc queries) | |||
Forecast function and Date option | Excel Worksheet Functions | |||
Forecast using 2 data sets | Excel Worksheet Functions | |||
Changing Forecast Data to Actual | Excel Discussion (Misc queries) | |||
Moving data in financial forecast | Excel Worksheet Functions |