vlookup help?

[trishnmaine]

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.

 

[trishnmaine]

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.

 

[Mattlynn via OfficeKB.com]

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

 

[Jacob Skaria]

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:B1000>0)*(WEEKDAY(B2:B1000)=3)*(B2:B1000>MAX(B2:B1000)-(4*7)),C2:C1000))


If this post helps click Yes

 

[trishnmaine]

That works! Now what if I want to calculate for the last 7 Tuesdays based on
today's date?

 

[trishnmaine]

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?

 

[trishnmaine]

Hmmmm...this isn't working for Sunday when I use =1 in the Weekday formula.
Not sure why?

 

[berniean]

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.

 

[Jacob Skaria]

If this gets updated on a daily basis you can try

=AVERAGE(IF((B2:B1000>0)*(WEEKDAY(B2:B1000)=3)*(B2:B1000>TODAY()-(4*7)),C2:C1000))

'for your previous query for 7 weeks. Make sure you have minimum 7 weeks
data or otherwise it will return erro
=AVERAGE(IF((B2:B1000>0)*(WEEKDAY(B2:B1000)=3)*(B2:B1000>TODAY()-(7*7)),C2:C1000))


If this post helps click Yes