Average with non-numeric characters and blanks in range (?)

[bhodgman]

Need help with a formula for the average of the following range, ignoring the blanks and text cells ('DNP' and 'NA'), but treating any number preceded by a '^' as that number:

<table cellspacing="0" cellpadding="0" dir="ltr" border="1" fixed;="" font-family:="" arial,="" sans,="" sans-serif;="" border-collapse:="" collapse;="" border:="" 1px="" solid="" rgb(204,="" 204,="" 204);"="" width=""><colgroup><col style="width: 100px;"></colgroup><tbody>[TR]
[TD="align: right"]NA[/TD]
[/TR]
[TR]
[TD="align: right"]^4[/TD]
[/TR]
[TR]
[TD="align: right"]DNP[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]NA[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]^1[/TD]
[/TR]
</tbody></table>

So formula for average of the above would return an answer of '3' - (4+5+2+1 / these four values =3).

Thoughts? I think it needs to be array entered with some combination of SUBSTITUTE and ISNUMBER, but I just can't figure out the syntax... Thanks!!!

 

[Aladin Akyurek]

Thank you for trying, but that doesn't seem to work - that returns a value of '2' instead of '3'.

If the range is:

NA
(blank cell)
^3
2
^1
4
DNP

the formula needs to return a value of 2.5 (average of 3+2+1+4).

It returns 3 over here. And 2.5 for the above sample, see: https://dl.dropboxusercontent.com/u/65698317/bhodgman conditional average.xlsx

 

[AliGW]

Hmmm.... I'm actually using Google Sheets, and now noticed that if the blank cells are filled with some text, the formula seems to returns the correct value...

I thought the syntax between Google Sheets and Excel was essentially identical... but perhaps not... ugghh....

Are the 'blank' cells really blank or the result of a formula? If the latter, get the formula to return a single blank " " instead of "" and it will work in Google Sheets.

 

[Aladin Akyurek]

Hmmm.... I'm actually using Google Sheets, and now noticed that if the blank cells are filled with some text, the formula seems to returns the correct value...

I thought the syntax between Google Sheets and Excel was essentially identical... but perhaps not... ugghh....

Google Sheets has an issue with processing of empty/blanks cells... So try the following instead:

=arrayformula(AVERAGE(IF(1-(A2:A8=""),IF(ISNUMBER(SUBSTITUTE(A2:A8,"^","",1)+0),SUBSTITUTE(A2:A8,"^","",1)+0))))

 

[AliGW]

That results in this in Google Sheets:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

NA		3.666666667
^4
DNP
5
NA
2
^1

<tbody>
</tbody>