Hi there,
To declare my problem I'd use a simple example here. On the table below, I want to have the average of values in column "B" which are corresponded to the cells on column "A" with a value equal to cell "C2" or "101". As it can be seen in the table, some corresponded cells are empty (blank). If I use either of the formulas below, I would end up with a wrong value, since the blank cells in these formulas will interpreted as "0"!
=SUMIF($A$2:$A$15,C2,$B$2:$B$15)/COUNTIF($A$2:$A$15,C2)
or
=AVERAGE(IF($A$2:$A$15=C2,$B$2:$B$15)) (with CTRL+SHIFT+ENTER)
Any idea for a solution (Appreciate your help in advance)!?
A B C
100 32.5 101
100 33.4
100 34.5
100 31.6
100 34.9
101 38.4
101
101
101 39.1
101 31.1
102 32.4
102 34.1
102 33.3
102 34.9
To declare my problem I'd use a simple example here. On the table below, I want to have the average of values in column "B" which are corresponded to the cells on column "A" with a value equal to cell "C2" or "101". As it can be seen in the table, some corresponded cells are empty (blank). If I use either of the formulas below, I would end up with a wrong value, since the blank cells in these formulas will interpreted as "0"!
=SUMIF($A$2:$A$15,C2,$B$2:$B$15)/COUNTIF($A$2:$A$15,C2)
or
=AVERAGE(IF($A$2:$A$15=C2,$B$2:$B$15)) (with CTRL+SHIFT+ENTER)
Any idea for a solution (Appreciate your help in advance)!?
A B C
100 32.5 101
100 33.4
100 34.5
100 31.6
100 34.9
101 38.4
101
101
101 39.1
101 31.1
102 32.4
102 34.1
102 33.3
102 34.9
