Help w/ calculating weekly Avg

[Cam]

Hello,

I am not sure what formula to use to calculate the weekly average, which is
the range of the week data (Monday to Sunday). Example: date range from 2/4
to 2/10/08 week is the sum of data from 2/4 to 2/10 divided by 5 (weekday).
Below is a sample of data and results I want to achieve.

Data:
Type1 Type2 WeekAvg
Date ProdA ProdB ProdA ProdB ProdA ProdB
2/4/08 30 10 20 10 61 37
2/5/08 10 15 10 15 61 37
2/6/08 50 15 25 10 61 37
2/7/08 20 10 20 20 61 37
2/8/08 10 10 30 20 61 37
2/9/08 10 15 40 15
2/10/08 20 10 10 10

Result column: WeekAvg for ProdA = 61
(30+10+50+20+10+10+20+20+10+25+20+30+40+10) / 5

Result column: WeekAvg for ProdB = 37
(10+15+15+10+10+15+10+10+15+10+20+20+15+10) / 5

NOTE: 2/9 and 2/10 are weekend so does not need to show value.

 

[Jim Thomlinson]

Something like this should do the trick...
=IF(AND(WEEKDAY($A3)>1, WEEKDAY($A3)<7), (SUMPRODUCT(--($A$3:$A$9>=$A$11),
--($A$3:$A$9<=$A$12), B$3:B$9)+SUMPRODUCT(--($A$3:$A$9>=$A$11),
--($A$3:$A$9<=$A$12), D$3$9))/5, "")

My source data looked like this
A B C D E F G
2 Date ProdA ProdB ProdA ProdB
3 4-Feb-08 30 10 20 10 61 37
4 5-Feb-08 10 15 10 15 61 37
5 6-Feb-08 50 15 25 10 61 37
6 7-Feb-08 20 10 20 20 61 37
7 8-Feb-08 10 10 30 20 61 37
8 9-Feb-08 10 15 40 15
9 10-Feb-08 20 10 10 10
10
11 4-Feb-08
12 10-Feb-08

 

[Cam]

Jim,

Can you repost? when I click on your response. The screen is blank.

 

[Jim Thomlinson]

The news group seems to be having some difficulties. Try going directly to
the address...

http://www.microsoft.com/communitie...cef-6aac-414e-9881-2ba33de8be2f&lang=en&cr=US

 

[Cam]

Jim,

I tried to duplicated the same data as your sample and it is giving me
#VALUE!.
Also, Wondering cell A11 and A12. I cannot put those reference cell there,
cause there are hundred of other data to row hundreds that I am trying to
calculate the same thing.

What I did was I added another column after E to display the ending week
date so they are showing date 2/10/08 for row 3 to 9. Can't you reference
that instead?

Also, for learning purpose, what is the -- symbol represent? Thanks

 

[Cam]

Jim,

Jim,

I got it to work with your formula, but was wondering cell A11 and A12. I
cannot put those reference cell there, cause there are hundred of other data
to row hundreds that I am trying to calculate the same thing.

What I did was I added another column after E to display the ending week
date so they are showing date 2/10/08 for row 3 to 9. Can't you reference
that instead?

Also, for learning purpose, what is the -- symbol represent? Thanks

 

[Jim Thomlinson]

Check out this link...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Basically it is coercing true / false into 1 /0