how can I average more cells only in a column by a criteria?

[Guest]

Hello.

I have the folowing situation:
A B
01 Day Val
02 1 12
03 2 15
04 3 18
05 1 10
06 2 22
07 3 17
08 4 19
09 1 25
10 2 10
11
12
13
14

The numbers in col "A" are a result of a fomula (weekday..etc) and is
changing conditionated by a date.
The numbers in col "B" are the result of other calculations (ex. d7-d6 or
etc.)
How can i AVERAGE at B12 all the values from B2:B10 but only for the
coresponding day=1 ? (and at B13 all values for day=2, at B14 -> day=3, and
so on).
And if the numbers in col "A" they are change .... the result at B14 to
reflect the new changes as well !! I mean ALWAYS at B12 must be the average
of all values coresponding to day=1 ! (ex. b2, b5, b9)

Does anybody understood what do I need ?

Please help !!!!

 

[M. Authement]

Try this formula. Note that it is an array formula and must be entered
using Ctrl+Shift+Enter

=AVERAGE(IF($A$2:$A$10=1,$B2:$B10))

If you put the 1 in cell A12 and replace the 1 in the formula above with
A12, then you could put 2,3,etc. in cells A13, A14, etc. and copy the
formula down.

Hope that helps!

 

[Guest]

thx for your quick answer, M. Authement !!
is working, but .....

on col "B" not all the cells have values.

Example
___A____B
01 day val
02 1 1
03 2 2
04 3 3
05 4 4
06 5 5
07 1 1
08 2
09 3
10 4
11 5
12 1
13 2
14 3
15 4
16 5
17 1
18 2
19 3
20 4
21 5
22
23 0,5

At B23 formula is{ =AVERAGE(IF($A$2:$A$21=1;$B2:$B21))}
the result is ... divided at ALL cells counted in col "A"... to be equal
with 1
I do not want that !!
For the upper example the result should be ... 1 (b2+b7)/2=1
If the cell b12=1 the average should be (b2+b7+b12)/3=1
by your formula the result is for the first case is 0.5 and the second is
0.8 !!
If there are more cell that is not an average at all !!
I mean if only one cell has data ... the average is equal to that cell,
right ?
if only 2 cells have datas... the average is (cel1+cel2)/2. an so on ...

did you understand me ?
please help (again) ... i am feeling so close now ... please !!!

thx in advance !
Alinutza.

 

[Dave Peterson]

One way:

=SUMIF(A2:A21,1,B2:B21)/SUMPRODUCT(--(A2:A21=1),--ISNUMBER(B2:B21))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html