| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup help?
Hello - I am working on forecasting purchase of product based on sales
history. Sales are dependent on the day of the week. So I have data for 3+ months and I want to predict what to purchase for Tuesday based on the last 7 Tuesdays sales. Here's a sample of the data: Day Date Sold Fri 05/01/09 3,006 Sat 05/02/09 3,272 Sun 05/03/09 2,151 Mon 05/04/09 2,033 Tue 05/05/09 3,272 Wed 05/06/09 2,348 Thu 05/07/09 2,284 Fri 05/08/09 3,159 Sat 05/09/09 4,086 Sun 05/10/09 2,398 Mon 05/11/09 2,189 Tue 05/12/09 2,399 Wed 05/13/09 2,426 Thu 05/14/09 2,894 Fri 05/15/09 2,860 Sat 05/16/09 3,571 Sun 05/17/09 3,138 Mon 05/18/09 4,518 Tue 05/19/09 2,896 Wed 05/20/09 2,920 Thu 05/21/09 2,457 Fri 05/22/09 3,101 Sat 05/23/09 3,423 Sun 05/24/09 2,939 Mon 05/25/09 2,976 Tue 05/26/09 2,195 Wed 05/27/09 2,613 Thu 05/28/09 3,250 The 1st column is the day of the week. The 2nd column is the date 3. The 4th column is the number of product sold on that date. So for this example, I want to look at Tuesday sales. I want to average the sales over the past 4 Tuesdays so I can predict what to purchase for next Tuesday inventory. I started fooling around with vlookup and index functions but just haven't been able to work this out. Any help would be much appreciated! Thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup help?
Sorry, typo in the paragraph describing the data. Restated:
The 1st column is the day of the week. The 2nd column is the date. The 3rd column is the number of product sold on that date. "trishnmaine" wrote: Hello - I am working on forecasting purchase of product based on sales history. Sales are dependent on the day of the week. So I have data for 3+ months and I want to predict what to purchase for Tuesday based on the last 7 Tuesdays sales. Here's a sample of the data: Day Date Sold Fri 05/01/09 3,006 Sat 05/02/09 3,272 Sun 05/03/09 2,151 Mon 05/04/09 2,033 Tue 05/05/09 3,272 Wed 05/06/09 2,348 Thu 05/07/09 2,284 Fri 05/08/09 3,159 Sat 05/09/09 4,086 Sun 05/10/09 2,398 Mon 05/11/09 2,189 Tue 05/12/09 2,399 Wed 05/13/09 2,426 Thu 05/14/09 2,894 Fri 05/15/09 2,860 Sat 05/16/09 3,571 Sun 05/17/09 3,138 Mon 05/18/09 4,518 Tue 05/19/09 2,896 Wed 05/20/09 2,920 Thu 05/21/09 2,457 Fri 05/22/09 3,101 Sat 05/23/09 3,423 Sun 05/24/09 2,939 Mon 05/25/09 2,976 Tue 05/26/09 2,195 Wed 05/27/09 2,613 Thu 05/28/09 3,250 The 1st column is the day of the week. The 2nd column is the date 3. The 4th column is the number of product sold on that date. So for this example, I want to look at Tuesday sales. I want to average the sales over the past 4 Tuesdays so I can predict what to purchase for next Tuesday inventory. I started fooling around with vlookup and index functions but just haven't been able to work this out. Any help would be much appreciated! Thanks. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup help?
I would suggest arranging in a pivot table.
Put "day" in the row area, "sold" in the Data area and set the sold to the "average" function - it wil prob be defaulted to sum This would update very qucikly also and can be used going forward as a model Matt trishnmaine wrote: Hello - I am working on forecasting purchase of product based on sales history. Sales are dependent on the day of the week. So I have data for 3+ months and I want to predict what to purchase for Tuesday based on the last 7 Tuesdays sales. Here's a sample of the data: Day Date Sold Fri 05/01/09 3,006 Sat 05/02/09 3,272 Sun 05/03/09 2,151 Mon 05/04/09 2,033 Tue 05/05/09 3,272 Wed 05/06/09 2,348 Thu 05/07/09 2,284 Fri 05/08/09 3,159 Sat 05/09/09 4,086 Sun 05/10/09 2,398 Mon 05/11/09 2,189 Tue 05/12/09 2,399 Wed 05/13/09 2,426 Thu 05/14/09 2,894 Fri 05/15/09 2,860 Sat 05/16/09 3,571 Sun 05/17/09 3,138 Mon 05/18/09 4,518 Tue 05/19/09 2,896 Wed 05/20/09 2,920 Thu 05/21/09 2,457 Fri 05/22/09 3,101 Sat 05/23/09 3,423 Sun 05/24/09 2,939 Mon 05/25/09 2,976 Tue 05/26/09 2,195 Wed 05/27/09 2,613 Thu 05/28/09 3,250 The 1st column is the day of the week. The 2nd column is the date 3. The 4th column is the number of product sold on that date. So for this example, I want to look at Tuesday sales. I want to average the sales over the past 4 Tuesdays so I can predict what to purchase for next Tuesday inventory. I started fooling around with vlookup and index functions but just haven't been able to work this out. Any help would be much appreciated! Thanks. -- Matt Lynn Message posted via http://www.officekb.com |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup help?
Try the below. Please note that this is an array formula. An array formula
can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" Col B date Col C Sold =AVERAGE(IF((B2:B10000)*(WEEKDAY(B2:B1000)=3)*(B2 :B1000MAX(B2:B1000)-(4*7)),C2:C1000)) If this post helps click Yes --------------- Jacob Skaria "trishnmaine" wrote: Hello - I am working on forecasting purchase of product based on sales history. Sales are dependent on the day of the week. So I have data for 3+ months and I want to predict what to purchase for Tuesday based on the last 7 Tuesdays sales. Here's a sample of the data: Day Date Sold Fri 05/01/09 3,006 Sat 05/02/09 3,272 Sun 05/03/09 2,151 Mon 05/04/09 2,033 Tue 05/05/09 3,272 Wed 05/06/09 2,348 Thu 05/07/09 2,284 Fri 05/08/09 3,159 Sat 05/09/09 4,086 Sun 05/10/09 2,398 Mon 05/11/09 2,189 Tue 05/12/09 2,399 Wed 05/13/09 2,426 Thu 05/14/09 2,894 Fri 05/15/09 2,860 Sat 05/16/09 3,571 Sun 05/17/09 3,138 Mon 05/18/09 4,518 Tue 05/19/09 2,896 Wed 05/20/09 2,920 Thu 05/21/09 2,457 Fri 05/22/09 3,101 Sat 05/23/09 3,423 Sun 05/24/09 2,939 Mon 05/25/09 2,976 Tue 05/26/09 2,195 Wed 05/27/09 2,613 Thu 05/28/09 3,250 The 1st column is the day of the week. The 2nd column is the date 3. The 4th column is the number of product sold on that date. So for this example, I want to look at Tuesday sales. I want to average the sales over the past 4 Tuesdays so I can predict what to purchase for next Tuesday inventory. I started fooling around with vlookup and index functions but just haven't been able to work this out. Any help would be much appreciated! Thanks. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup help?
That works! Now what if I want to calculate for the last 7 Tuesdays based on
today's date? "Jacob Skaria" wrote: Try the below. Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" Col B date Col C Sold =AVERAGE(IF((B2:B10000)*(WEEKDAY(B2:B1000)=3)*(B2 :B1000MAX(B2:B1000)-(4*7)),C2:C1000)) If this post helps click Yes --------------- Jacob Skaria "trishnmaine" wrote: Hello - I am working on forecasting purchase of product based on sales history. Sales are dependent on the day of the week. So I have data for 3+ months and I want to predict what to purchase for Tuesday based on the last 7 Tuesdays sales. Here's a sample of the data: Day Date Sold Fri 05/01/09 3,006 Sat 05/02/09 3,272 Sun 05/03/09 2,151 Mon 05/04/09 2,033 Tue 05/05/09 3,272 Wed 05/06/09 2,348 Thu 05/07/09 2,284 Fri 05/08/09 3,159 Sat 05/09/09 4,086 Sun 05/10/09 2,398 Mon 05/11/09 2,189 Tue 05/12/09 2,399 Wed 05/13/09 2,426 Thu 05/14/09 2,894 Fri 05/15/09 2,860 Sat 05/16/09 3,571 Sun 05/17/09 3,138 Mon 05/18/09 4,518 Tue 05/19/09 2,896 Wed 05/20/09 2,920 Thu 05/21/09 2,457 Fri 05/22/09 3,101 Sat 05/23/09 3,423 Sun 05/24/09 2,939 Mon 05/25/09 2,976 Tue 05/26/09 2,195 Wed 05/27/09 2,613 Thu 05/28/09 3,250 The 1st column is the day of the week. The 2nd column is the date 3. The 4th column is the number of product sold on that date. So for this example, I want to look at Tuesday sales. I want to average the sales over the past 4 Tuesdays so I can predict what to purchase for next Tuesday inventory. I started fooling around with vlookup and index functions but just haven't been able to work this out. Any help would be much appreciated! Thanks. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup help?
Thanks! I see how that works. The question is, can I set it to
automatically look at the last 7 weeks based on todays date? Or do I have to reset the data range? "Mattlynn via OfficeKB.com" wrote: I would suggest arranging in a pivot table. Put "day" in the row area, "sold" in the Data area and set the sold to the "average" function - it wil prob be defaulted to sum This would update very qucikly also and can be used going forward as a model Matt trishnmaine wrote: Hello - I am working on forecasting purchase of product based on sales history. Sales are dependent on the day of the week. So I have data for 3+ months and I want to predict what to purchase for Tuesday based on the last 7 Tuesdays sales. Here's a sample of the data: Day Date Sold Fri 05/01/09 3,006 Sat 05/02/09 3,272 Sun 05/03/09 2,151 Mon 05/04/09 2,033 Tue 05/05/09 3,272 Wed 05/06/09 2,348 Thu 05/07/09 2,284 Fri 05/08/09 3,159 Sat 05/09/09 4,086 Sun 05/10/09 2,398 Mon 05/11/09 2,189 Tue 05/12/09 2,399 Wed 05/13/09 2,426 Thu 05/14/09 2,894 Fri 05/15/09 2,860 Sat 05/16/09 3,571 Sun 05/17/09 3,138 Mon 05/18/09 4,518 Tue 05/19/09 2,896 Wed 05/20/09 2,920 Thu 05/21/09 2,457 Fri 05/22/09 3,101 Sat 05/23/09 3,423 Sun 05/24/09 2,939 Mon 05/25/09 2,976 Tue 05/26/09 2,195 Wed 05/27/09 2,613 Thu 05/28/09 3,250 The 1st column is the day of the week. The 2nd column is the date 3. The 4th column is the number of product sold on that date. So for this example, I want to look at Tuesday sales. I want to average the sales over the past 4 Tuesdays so I can predict what to purchase for next Tuesday inventory. I started fooling around with vlookup and index functions but just haven't been able to work this out. Any help would be much appreciated! Thanks. -- Matt Lynn Message posted via http://www.officekb.com |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup help?
Hmmmm...this isn't working for Sunday when I use =1 in the Weekday formula.
Not sure why? "Jacob Skaria" wrote: Try the below. Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" Col B date Col C Sold =AVERAGE(IF((B2:B10000)*(WEEKDAY(B2:B1000)=3)*(B2 :B1000MAX(B2:B1000)-(4*7)),C2:C1000)) If this post helps click Yes --------------- Jacob Skaria "trishnmaine" wrote: Hello - I am working on forecasting purchase of product based on sales history. Sales are dependent on the day of the week. So I have data for 3+ months and I want to predict what to purchase for Tuesday based on the last 7 Tuesdays sales. Here's a sample of the data: Day Date Sold Fri 05/01/09 3,006 Sat 05/02/09 3,272 Sun 05/03/09 2,151 Mon 05/04/09 2,033 Tue 05/05/09 3,272 Wed 05/06/09 2,348 Thu 05/07/09 2,284 Fri 05/08/09 3,159 Sat 05/09/09 4,086 Sun 05/10/09 2,398 Mon 05/11/09 2,189 Tue 05/12/09 2,399 Wed 05/13/09 2,426 Thu 05/14/09 2,894 Fri 05/15/09 2,860 Sat 05/16/09 3,571 Sun 05/17/09 3,138 Mon 05/18/09 4,518 Tue 05/19/09 2,896 Wed 05/20/09 2,920 Thu 05/21/09 2,457 Fri 05/22/09 3,101 Sat 05/23/09 3,423 Sun 05/24/09 2,939 Mon 05/25/09 2,976 Tue 05/26/09 2,195 Wed 05/27/09 2,613 Thu 05/28/09 3,250 The 1st column is the day of the week. The 2nd column is the date 3. The 4th column is the number of product sold on that date. So for this example, I want to look at Tuesday sales. I want to average the sales over the past 4 Tuesdays so I can predict what to purchase for next Tuesday inventory. I started fooling around with vlookup and index functions but just haven't been able to work this out. Any help would be much appreciated! Thanks. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup help?
You have two choices: either adjust the data range of your sales history
report each time you run the pivot table, or, if you have access to the query that provides the sales history report, restrict the date range of the query. "trishnmaine" wrote: Thanks! I see how that works. The question is, can I set it to automatically look at the last 7 weeks based on todays date? Or do I have to reset the data range? "Mattlynn via OfficeKB.com" wrote: I would suggest arranging in a pivot table. Put "day" in the row area, "sold" in the Data area and set the sold to the "average" function - it wil prob be defaulted to sum This would update very qucikly also and can be used going forward as a model Matt trishnmaine wrote: Hello - I am working on forecasting purchase of product based on sales history. Sales are dependent on the day of the week. So I have data for 3+ months and I want to predict what to purchase for Tuesday based on the last 7 Tuesdays sales. Here's a sample of the data: Day Date Sold Fri 05/01/09 3,006 Sat 05/02/09 3,272 Sun 05/03/09 2,151 Mon 05/04/09 2,033 Tue 05/05/09 3,272 Wed 05/06/09 2,348 Thu 05/07/09 2,284 Fri 05/08/09 3,159 Sat 05/09/09 4,086 Sun 05/10/09 2,398 Mon 05/11/09 2,189 Tue 05/12/09 2,399 Wed 05/13/09 2,426 Thu 05/14/09 2,894 Fri 05/15/09 2,860 Sat 05/16/09 3,571 Sun 05/17/09 3,138 Mon 05/18/09 4,518 Tue 05/19/09 2,896 Wed 05/20/09 2,920 Thu 05/21/09 2,457 Fri 05/22/09 3,101 Sat 05/23/09 3,423 Sun 05/24/09 2,939 Mon 05/25/09 2,976 Tue 05/26/09 2,195 Wed 05/27/09 2,613 Thu 05/28/09 3,250 The 1st column is the day of the week. The 2nd column is the date 3. The 4th column is the number of product sold on that date. So for this example, I want to look at Tuesday sales. I want to average the sales over the past 4 Tuesdays so I can predict what to purchase for next Tuesday inventory. I started fooling around with vlookup and index functions but just haven't been able to work this out. Any help would be much appreciated! Thanks. -- Matt Lynn Message posted via http://www.officekb.com |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup help?
If this gets updated on a daily basis you can try
=AVERAGE(IF((B2:B10000)*(WEEKDAY(B2:B1000)=3)*(B2 :B1000TODAY()-(4*7)),C2:C1000)) 'for your previous query for 7 weeks. Make sure you have minimum 7 weeks data or otherwise it will return error =AVERAGE(IF((B2:B10000)*(WEEKDAY(B2:B1000)=3)*(B2 :B1000TODAY()-(7*7)),C2:C1000)) If this post helps click Yes --------------- Jacob Skaria "trishnmaine" wrote: Hmmmm...this isn't working for Sunday when I use =1 in the Weekday formula. Not sure why? "Jacob Skaria" wrote: Try the below. Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" Col B date Col C Sold =AVERAGE(IF((B2:B10000)*(WEEKDAY(B2:B1000)=3)*(B2 :B1000MAX(B2:B1000)-(4*7)),C2:C1000)) If this post helps click Yes --------------- Jacob Skaria "trishnmaine" wrote: Hello - I am working on forecasting purchase of product based on sales history. Sales are dependent on the day of the week. So I have data for 3+ months and I want to predict what to purchase for Tuesday based on the last 7 Tuesdays sales. Here's a sample of the data: Day Date Sold Fri 05/01/09 3,006 Sat 05/02/09 3,272 Sun 05/03/09 2,151 Mon 05/04/09 2,033 Tue 05/05/09 3,272 Wed 05/06/09 2,348 Thu 05/07/09 2,284 Fri 05/08/09 3,159 Sat 05/09/09 4,086 Sun 05/10/09 2,398 Mon 05/11/09 2,189 Tue 05/12/09 2,399 Wed 05/13/09 2,426 Thu 05/14/09 2,894 Fri 05/15/09 2,860 Sat 05/16/09 3,571 Sun 05/17/09 3,138 Mon 05/18/09 4,518 Tue 05/19/09 2,896 Wed 05/20/09 2,920 Thu 05/21/09 2,457 Fri 05/22/09 3,101 Sat 05/23/09 3,423 Sun 05/24/09 2,939 Mon 05/25/09 2,976 Tue 05/26/09 2,195 Wed 05/27/09 2,613 Thu 05/28/09 3,250 The 1st column is the day of the week. The 2nd column is the date 3. The 4th column is the number of product sold on that date. So for this example, I want to look at Tuesday sales. I want to average the sales over the past 4 Tuesdays so I can predict what to purchase for next Tuesday inventory. I started fooling around with vlookup and index functions but just haven't been able to work this out. Any help would be much appreciated! Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Linear Mode
