Average in Excel

niladri2005

Board Regular
Joined
Sep 21, 2012
Messages
109
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!!
 
Last edited:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!!
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
@Arithos First Reply

I am also trying to get the reulst what you are suggesting. Let's see what happens!
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top