using an argument of "not equal" to an error

[snipescc]

Hi folks. I have a spreadsheet that has lots of #ref! errors. They are there because I put formulas into cells before there is data to fill them. The data will be put there later by a macro. So I can't eliminate the errors. However, I need to find the average of numbers in non-adjacent cells, excluding any cell with a #ref! error. I've had trouble writing any formula that contains <>#ref! or something similar. Is there a trick to excluding cells with errors in them? With some things I've seen quotes around both the '<>' and the '#ref!', but while it seems to work when I copy someone else's formula from the web, I have had no luck writing it into formulas myself. Any tips?

 

[Aladin Akyurek]

Hi folks. I have a spreadsheet that has lots of #ref! errors. They are there because I put formulas into cells before there is data to fill them. The data will be put there later by a macro. So I can't eliminate the errors. However, I need to find the average of numbers in non-adjacent cells, excluding any cell with a #ref! error. I've had trouble writing any formula that contains <>#ref! or something similar. Is there a trick to excluding cells with errors in them? With some things I've seen quotes around both the '<>' and the '#ref!', but while it seems to work when I copy someone else's formula from the web, I have had no luck writing it into formulas myself. Any tips?

When doing numeric processing on error-ridden ranges...

Control+shift+enter (CSE), not just enter:

=AVERAGE(IF(ISNUMBER(Range),Range)

Such would admit other stats like SUM, MAX, MIN, etc.

For SUM, non-CSE aproaches would be:

=SUMIF(Range,"<"&9.99E+307)

=SUM(SUMIF(Range,{"<0",">0"}))

The foregoing avoids all errors, not a specific type alone.