finding average - Urgent

[Vicky]

Please some one help me out in finding the average
Below we have 2 colums A and B, it looks like this
column A Column B
A012 35
A013 50
A014 25
A012 0
A013 0
A014 40
I want the average of the column B for the particular numbers A012, A013, A014

 

[Sheeloo]

Use this in C1
=SUMIF($A$1:$A$6,A1,$B$1:$B$6)/COUNTIF($A$1:$A$6,A1)
and copy down
This will give you the average for the corresponding value in Col A

 

[Vicky]

Oh! it is returning me the Zero value, i mean to say the column "B" has even
the zero value in that, but when we find the average it should ignore "zero"

Can you dig more into that

thanks for your time!!

Vicky

 

[smartin]

Please some one help me out in finding the average
Below we have 2 colums A and B, it looks like this
column A Column B
A012 35
A013 50
A014 25
A012 0
A013 0
A014 40
I want the average of the column B for the particular numbers A012, A013, A014

There are a couple ways to interpret your requirements.

One way: the average of B (having A with any value in A012, A013, A014).
Enter as an array* formula:

=AVERAGE(IF(A1:A99={"A012","A013","A014"},B1:B99))


Another way: the average of B (grouped by A) where A is A012, A013,
A014. Also an array* formula:

=AVERAGE(IF($A$1:$A$99="A012",$B$1:$B$99))

etc.

*Commit array formula by pressing Ctrl+Shift+Enter, not just Enter.

 

[Sheeloo]

Try
=SUMIF($A$1:$A$6,A1,$B$1:$B$6)/SUMPRODUCT(--($A$1:$A$6=A1),--($B$1:$B$6>0))

 

[Vicky]

Genius sheeloo, it worked

Thanks a lot!!!!!
Vicky