Date X Data


G

Guest

I have,
A B
1 5/1/2005
2 5/2/2005
3 5/3/2005
4 6/1/2005
5 6/2/2005
6 6/3/2005
7 7/1/2005
8 7/2/2005
9 7/3/2005
10 7/4/2005

I want the Average of the different months, May for example, why this:

=SUMPRODUCT(SUBTOTAL(1,A1:A10)*(MONTH(B1:B10)=5)*(YEAR(B1:B10)=2005))

is giving me 16.5 ?

TXS
 
Ad

Advertisements

D

Dave R.

You can use array entered:
=AVERAGE(IF(MONTH(B1:B6)=5,A1:A6))
(enter with ctrl-shift-enter at once)
or

if you want to use sumproduct, one way is:
=SUMPRODUCT((MONTH(B1:B6)=5)*(A1:A6))/SUMPRODUCT(--(MONTH(B1:B6)=5))
which does not need to be array entered.
 
Ad

Advertisements

G

Guest

the subtotal (1... will give you 5.5 effectively as a constant
you have three times when the rest of the equaiton is met, therefore you
are adding three 5.5 to give you 16.5

to use sumproduct to get the average
=SUMPRODUCT(A1:A10*(MONTH(B1:B10)=5)*(YEAR(B1:B10)=2005))/
=SUMPRODUCT((MONTH(B1:B10)=5)*(YEAR(B1:B10)=2005))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top