Jacon Scaria responded to my earlier question. My questions and his response
are below. Wondering ig I can take this further with the following: How
would I calculate for the last 7 Tuesdays based on today's date? Hoping
Jacob can answer or anyone else who has an idea.

Thanks! So glad I found this forum!
Patricia



__________________________________________________ __________
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:

Click to show or hide original message or reply text.


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.

In the formula: ...(4*7)...

4 represents the number of instances you want to include in the average. So,
for the last 7 instances change 4 to 7. And don't forget: array entered!

--
Biff
Microsoft Excel MVP


"trishnmaine" wrote in message
...
Jacon Scaria responded to my earlier question. My questions and his
response
are below. Wondering ig I can take this further with the following: How
would I calculate for the last 7 Tuesdays based on today's date? Hoping
Jacob can answer or anyone else who has an idea.

Thanks! So glad I found this forum!
Patricia



__________________________________________________ __________
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:

Click to show or hide original message or reply text.


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.