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

#### bhodgman

##### New Member
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!!!

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### bhodgman

##### New Member
Sorry, range should be:

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

#### sunny102

##### Board Regular
Hi, is it ok if a helper column is used?

##### MrExcel MVP
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

##### New Member
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

##### Banned
Seems to work OK here:

Excel 2016 (Windows) 32 bit
ABC
2NA2.5
3
4^3
52
6^1
74
8DNP
Sheet2
Cell Formulas
RangeFormula
C2{=AVERAGE(IF(ISNUMBER(SUBSTITUTE(A2:A9,"^","",1)+0),SUBSTITUTE(A2:A9,"^","",1)+0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

#### bhodgman

##### New Member
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

##### Banned
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?

Last edited:

#### bhodgman

##### New Member
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....

Replies
1
Views
949
Replies
0
Views
489
Replies
3
Views
932
Replies
0
Views
319
Replies
14
Views
2K

1,191,614
Messages
5,987,680
Members
440,104
Latest member
thigarette

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back