Results 1 to 9 of 9

Thread: find the average of a cell containing digits and excluding characters and blanks

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Seeker khanaran's Avatar
    Join Date
    Jan 2013
    Location
    Lenasia South ,South Africa
    Posts
    12
    Articles
    0
    Excel Version
    2019

    find the average of a cell containing digits and excluding characters and blanks

    Hi

    I am attaching a file that needs a little modification

    The formula averages a cell contents however if there are blanks it returns an error annd if there are characters it returns an error


    Thank you in anticipation for all help

    regards

    raj
    Attached Files Attached Files

  2. #2
    Seeker khanaran's Avatar
    Join Date
    Jan 2013
    Location
    Lenasia South ,South Africa
    Posts
    12
    Articles
    0
    Excel Version
    2019
    I did post a similar request in another forum but think my explanation was not clear enough so did not get a reply as yet

    Code:
    https://www.excelforum.com/excel-formulas-and-functions/1305524-finding-the-avrage-of-a-cell-contents-ignoring-blanks-and-characters.html
    



  3. #3
    Conjurer hossat's Avatar
    Join Date
    May 2015
    Location
    Algiers, Algeria
    Posts
    112
    Articles
    0
    Excel Version
    365
    Hi Khanaran,

    You can add IFERROR like this : =SUMPRODUCT(IFERROR(1*MID(D5,ROW(INDIRECT("1:"&LEN(D5))),1), 0))/5

    If it works, please select this answer as solution.

    Hossat
    Algiers - DZ
    numidiabi.wordpress.com

  4. #4
    Seeker khanaran's Avatar
    Join Date
    Jan 2013
    Location
    Lenasia South ,South Africa
    Posts
    12
    Articles
    0
    Excel Version
    2019
    Hi

    Thanx for your help but somehow its not working like the way I want it to do

    Raj

  5. #5
    Conjurer hossat's Avatar
    Join Date
    May 2015
    Location
    Algiers, Algeria
    Posts
    112
    Articles
    0
    Excel Version
    365
    Hi,

    It works for me. Can you share your file with the formula that I gave you to see the problem ?

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,691
    Articles
    0
    Excel Version
    365
    hossat's formula is fine as long as it's array-entered (Ctrl+Shift+Enter, not just Enter).

  7. #7
    Conjurer hossat's Avatar
    Join Date
    May 2015
    Location
    Algiers, Algeria
    Posts
    112
    Articles
    0
    Excel Version
    365
    Hi p45cal,

    Yes, I forgotten to mention that because I am using o365 with insider program, and thanks to the new array dynamic formulas, we need to validate the formula only by Enter

Posting Permissions

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