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

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" wrote:

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

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

"Sheeloo" wrote:

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" wrote:

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

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

"Vicky" wrote:

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

"Sheeloo" wrote:

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" wrote:

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

Genius sheeloo, it worked

Thanks a lot!!!!!
Vicky

"Sheeloo" wrote:

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

"Vicky" wrote:

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

"Sheeloo" wrote:

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" wrote:

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

Vicky wrote:
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.