MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Ignoring everything but numbers


Posted by Rich T on October 08, 2001 6:00 AM

I have a large spreadsheet with multiple formulas and links. I wish to find the average of a row containing some DIV#! cells and some blanks. I've tried SUMIF in conjunction with COUNT, AVERAGEA, etc. but no success. I can not just add a ">0" criteria because I have some negative values and as soon as I use "<>0" the formula includes the DIV#!s and it all goes wrong.
I'd like to make any cells that don't contain a number blank but I don't know how to combine 'IF' statements!
HELP!
Thanks in advance!


Posted by Paul on October 08, 2001 6:26 AM

Try using IS ERROR in your formulas like
=IF(ISERROR(AVERAGE(A1:A4)),"",AVERAGE(A1:A4))
this will return a blank cell instead of DIV#! this will let your other formulas work right

Posted by Aladin Akyurek on October 08, 2001 7:44 AM

Rich,

It would be much better to avoid producing the #DIV/0 values.

However, you can use the following array formula:

=AVERAGE(IF(ISNUMBER(A1:A5),A1:A5))

You need to hit CONTROL+SHIFT+ENTER, not just ENTER, in order to enter an array formula.

Aladin

=========