Results 1 to 9 of 9

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

  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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    107
    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
    107
    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,677
    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
    107
    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

  8. #8
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,366
    Articles
    0
    Excel Version
    Office 365 Subscription
    I don't think that feature is limited to the insider program any longer - I think it has been or is being rolled out to regular subscribers. However, if the OP has the standalone version 2019, then he won't have it.
    Ali
    Enthusiastic self-taught user of MS Excel!

  9. #9
    Conjurer hossat's Avatar
    Join Date
    May 2015
    Location
    Algiers, Algeria
    Posts
    107
    Articles
    0
    Excel Version
    365
    Ok, ok, thank you AliGW for the information

Posting Permissions

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