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

khanaran

New member
Joined
Jan 23, 2013
Messages
12
Reaction score
0
Points
0
Location
Lenasia South ,South Africa
Excel Version(s)
2019
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
 

Attachments

  • avrage error.xlsx
    11.3 KB · Views: 13
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:
[FONT=Verdana]https://www.excelforum.com/excel-formulas-and-functions/1305524-finding-the-avrage-of-a-cell-contents-ignoring-blanks-and-characters.html
[/FONT]


 
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
 
Hi,

It works for me. Can you share your file with the formula that I gave you to see the problem ?
 
hossat's formula is fine as long as it's array-entered (Ctrl+Shift+Enter, not just Enter).
 
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
 
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.
 
Back
Top