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!!!

 

[bhodgman]

Sorry, range should be:


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

 

[sunny102]

Hi, is it ok if a helper column is used?

 

[Aladin Akyurek]

Sorry, range should be:


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

Control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER(SUBSTITUTE(A2:A9,"^","",1)+0),SUBSTITUTE(A2:A9,"^","",1)+0))

 

[bhodgman]

Control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER(SUBSTITUTE(A2:A9,"^","",1)+0),SUBSTITUTE(A2:A9,"^","",1)+0))

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

 

[AliGW]

Seems to work OK here:

Excel 2016 (Windows) 32 bit
	A	B	C
2	NA		2.5
3
4	^3
5	2
6	^1
7	4
8	DNP
Sheet2

Cell Formulas
Range	Formula
C2	{=AVERAGE(IF(ISNUMBER(SUBSTITUTE(A2:A9,"^","",1)+0),SUBSTITUTE(A2:A9,"^","",1)+0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bhodgman]

Note the '(blank cell)' means an empty cell -- it doesn't actually contain the text '(blank cell)'.

thus =AVERAGE(IF(ISNUMBER(SUBSTITUTE(A2:A9,"^","",1)+0),SUBSTITUTE(A2:A9,"^","",1)+0))

returns 2.4 with that second cell in the range as empty...

 

[AliGW]

There is something odd about your data, then: is one of the numbers actually text? Are the numbers all appearing flush left or right in their cells?

It works here correctly on both sets of data provided by you so far.

EDIT: Is that 'blank' cell really blank, or does it contain a formula? If so, what is the formula?

 

[bhodgman]

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

 

[AliGW]

Ah! I don't have access to Google Sheets - sorry. Maybe Aladin does.