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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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 !!!!
 
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!
 
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.
 
Back
Top