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!!

 

[Arithos]

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.
Just drag it down after, as long as its just 4 columns its an easy one.

Can you please guide me?

Thanks in advance!!

Well, you can do it like this:

=AVERAGE(IF(ISNUMBER(A2);A2;0)+IF(ISNUMBER(B2);B2;0)+IF(ISNUMBER(C2);C2;0)+IF(ISNUMBER(D2);D2;0))

the ISNUMBER just checks if its a number in that cell, and if TRUE, it returns the number for calculation, by the average formula.

 

[XOR LX]

Hi.

What formula are you using in E1? What error do you get?

AVERAGE ignores any text values (which is what "-" is, unless that's the result of some cell formatting in those cells?), so there should be no reason why:

=AVERAGE(A1:D1)

does not work.

Perhaps you are getting a #DIV/0! error? In that case, it may be that your numbers in those cells are formatted as text, not number. To check, enter this formula in a cell somewhere:

=ISNUMBER(A1)

What result do you get?

Perhaps repeat this formula for the other values in your range which "look" like numbers, but may actually be text representations of numbers.

Regards

 

[Arithos]

Just realized you would have to do it like this to get the average. (for some reason)

=AVERAGE(IF(ISNUMBER(A2);A2;0)+IF(ISNUMBER(B2);B2;0)+IF(ISNUMBER(C2);C2;0)+IF(ISNUMBER(D2);D2;0))/COUNTA(A2:D2)

You could also "re-create" the matrix you have going on with this formula,

=VALUE(SUBSTITUTE(A2;"-";0)) 'Do this for all cells, and create new 4 columns with just numbers, if there was a "-", then it will now say "0".

And you do the average

 

[niladri2005]

Hi

Thanks for quick reply..yes your formula is checking whether it is number or not. But my point is that if it is not number then it will not be counted at the time of average. For above example for first row it should me show me the result 18.5 but it is now showing 37.

However thanks!!

 

[XOR LX]

Hi

Thanks for quick reply..yes your formula is checking whether it is number or not. But my point is that if it is not number then it will not be counted at the time of average. For above example for first row it should me show me the result 18.5 but it is now showing 37.

However thanks!!

To whose posts are you replying?

Regards

 

[Aladin Akyurek]

Hi

Thanks for quick reply..yes your formula is checking whether it is number or not. But my point is that if it is not number then it will not be counted at the time of average. For above example for first row it should me show me the result 18.5 but it is now showing 37.

However thanks!!

E1:

=AVERAGE(A1:D1)

yields 18.5 for me, as it should.

Did you perhaps invoke

=SUM(A1:D1)

instead?

 

[niladri2005]

@Arithos First Reply

I am also trying to get the reulst what you are suggesting. Let's see what happens!

 

[XOR LX]

E1:

=AVERAGE(A1:D1)

yields 18.5 for me, as it should.

But it won't if those values are stored as text. That's why I asked the OP to check with ISNUMBER.

Regards

 

[kudakaswa14]

Hi I have two columns

Column A has numbers while some rows are blank
Column B has a drop down list whereby you classify the numbers in Column A e.g. asset, expense, revenue etc.

I would like a formula which will return "Please complete SCOA classification in full" where there is a number in column A but individual hasn't classified in column B and where all cells that have numbers have been classified to return "SCOA classification complete"

Posted as separate thread here http://www.mrexcel.com/forum/excel-questions/802092-if-statement-conundrum.html