# Average in Excel

##### Board Regular
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.

Last edited:

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### Arithos

##### Well-known Member
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.

#### XOR LX

##### Well-known Member
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

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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

##### MrExcel MVP
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)

##### Board Regular

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

#### XOR LX

##### Well-known Member
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

##### New Member
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

Last edited by a moderator:

Replies
3
Views
433
Replies
1
Views
143
Replies
4
Views
63
Replies
13
Views
468
Replies
11
Views
193

1,195,619
Messages
6,010,736
Members
441,567
Latest member
Flitbee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back