Thanks:  0
Likes:  0

1. ## Average in Excel

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.

2. ## Re: Average in Excel

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.

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.

3. ## Re: Average in Excel

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

4. ## Re: Average in Excel

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

5. ## Re: Average in Excel

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

6. ## Re: Average in Excel

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

7. ## Re: Average in Excel

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)

8. ## Re: Average in Excel

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

9. ## Re: Average in Excel

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

10. ## Re: Average in Excel

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-q...conundrum.html

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•