Help w/ calculating weekly Avg

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cam - 14 Mar 2008 17:05 GMT

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.

Reply to this Message

Jim Thomlinson - 14 Mar 2008 18:19 GMT

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:D$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

Signature

HTH...

Jim Thomlinson

> Hello,
>
[quoted text clipped - 21 lines]
>
> NOTE: 2/9 and 2/10 are weekend so does not need to show value.

Reply to this Message

Cam - 14 Mar 2008 19:37 GMT

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

> Something like this should do the trick...
> =IF(AND(WEEKDAY($A3)>1, WEEKDAY($A3)<7), (SUMPRODUCT(--($A$3:$A$9>=$A$11),
[quoted text clipped - 41 lines]
> >
> > NOTE: 2/9 and 2/10 are weekend so does not need to show value.

Reply to this Message

Cam - 14 Mar 2008 18:58 GMT

Jim,

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

> Hello,
>
[quoted text clipped - 21 lines]
>
> NOTE: 2/9 and 2/10 are weekend so does not need to show value.

Reply to this Message

Jim Thomlinson - 14 Mar 2008 19:03 GMT

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

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.
public.excel.misc&cat=en_US_34a4dcef-6aac-414e-9881-2ba33de8be2f&lang=en&cr=US

Signature

HTH...

Jim Thomlinson

> Jim,
>
[quoted text clipped - 25 lines]
> >
> > NOTE: 2/9 and 2/10 are weekend so does not need to show value.

Reply to this Message

Cam - 14 Mar 2008 20:16 GMT

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

> The news group seems to be having some difficulties. Try going directly to
> the address...
[quoted text clipped - 30 lines]
> > >
> > > NOTE: 2/9 and 2/10 are weekend so does not need to show value.

Reply to this Message

Jim Thomlinson - 14 Mar 2008 20:28 GMT

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

Signature

HTH...

Jim Thomlinson

> Jim,
>
[quoted text clipped - 44 lines]
> > > >
> > > > NOTE: 2/9 and 2/10 are weekend so does not need to show value.

Reply to this Message