Average in Excel

[niladri2005]

Hi All,

I have four colums. I wan to get the average from it. For Example

A B C D
12 - 25 -
- 45 - -
- - 17 34


when I am writing formula in E1 I am getting error for "-" sign. What I want is that where there is "-" sign it should avoid that cell and give me the average of the remaining cell. I am trying with if and and formula but no result found.

Can you please guide me?

Thanks in advance!!

 

[niladri2005]

Hi All,

I have used this formula in E1 and

=AVERAGE(IF(ISNUMBER(A1),A1,0)+IF(ISNUMBER(B1),B1,0)+IF(ISNUMBER(C1),C1,0)+IF(ISNUMBER(D1),D1,0)) and getting result 37. It should show me 18.5

 

[Arithos]

E1:

=AVERAGE(A1:D1)

yields 18.5 for me, as it should.

Did you perhaps invoke

=SUM(A1:D1)

instead?

Indeed: in cell E1 this formula does it, =AVERAGE(A1:D1)

He might not be using the AVERAGE formula as you implied. However I thought he would like avg of all columns, regardless of number/not number. (9,25 for first row)


This does not work for y ou niladri?

 

[Arithos]

Hi All,

I have used this formula in E1 and

=AVERAGE(IF(ISNUMBER(A1),A1,0)+IF(ISNUMBER(B1),B1,0)+IF(ISNUMBER(C1),C1,0)+IF(ISNUMBER(D1),D1,0)) and getting result 37. It should show me 18.5

Yes, you should just use =AVERAGE(A1:D1)

I thought you wanted average for all 4 columns, given that a "-" did mean zero.

PS: I noticed your problem and revized it above.(added COUNTA, after the formula you are now using)

 

[niladri2005]

Yes...Now i got it...

when thee are four "-" then I am getting #DIV/0! error. To fix this issue I need to apply iferror...


Thanks you all!!!

 

[Arithos]

Yes...Now i got it...

when thee are four "-" then I am getting #DIV/0! error. To fix this issue I need to apply iferror...


Thanks you all!!!

Yes, Iferror. And, goodie

 

[Aladin Akyurek]

Yes...Now i got it...

when thee are four "-" then I am getting #DIV/0! error. To fix this issue I need to apply iferror...


Thanks you all!!!

You did not explain why you had 37 for the first record!...

=IFERROR(AVERAGE(A1:D1),"")

appears what you need. If you don't have IFERROR, try:

=SUM(A1:D1)/MAX(1,COUNT(A1:D1))