Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Average in Excel

  1. #1
    Board Regular
    Join Date
    Sep 2012
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

    Can you please guide me?

    Thanks in advance!!
    Last edited by niladri2005; Aug 29th, 2014 at 04:01 AM.

  2. #2
    Board Regular
    Join Date
    Aug 2014
    Location
    Norway
    Posts
    598
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average in Excel

    Quote Originally Posted by niladri2005 View Post
    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.
    Regards,
    Sturla

    - I try to post my changes in your code in RED
    - If posting VBA code, please use Code Tags


  3. #3
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,473
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Board Regular
    Join Date
    Aug 2014
    Location
    Norway
    Posts
    598
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Regards,
    Sturla

    - I try to post my changes in your code in RED
    - If posting VBA code, please use Code Tags


  5. #5
    Board Regular
    Join Date
    Sep 2012
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,473
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average in Excel

    Quote Originally Posted by niladri2005 View Post
    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
    Advanced Excel Techniques: http://excelxor.com/

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Average in Excel

    Quote Originally Posted by niladri2005 View Post
    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?
    Assuming too much and qualifying too much are two faces of the same problem.

  8. #8
    Board Regular
    Join Date
    Sep 2012
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average in Excel

    @Arithos First Reply

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

  9. #9
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,473
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average in Excel

    Quote Originally Posted by Aladin Akyurek View Post
    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
    Advanced Excel Techniques: http://excelxor.com/

  10. #10
    New Member
    Join Date
    Aug 2014
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by VoG; Aug 29th, 2014 at 05:05 AM. Reason: Added link to new thread - moderator

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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