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