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?

 

[HaHoBe]

Hi, snipescc,

I check the cells for contents in an if-statement and only if there is a value let the formula do the work, if not either show a blank or zero (0). This way around you won´t have to deal with error. SpecialCells(xlCellTypeFormulas, 7) might help as errors shall not be included.

Ciao,
Holger

 

[pgc01]

Hi

The post is not clear. You are after a workseet solution or a vba solution (or mixed)?

 

[snipescc]

A worksheet solution. At heart what i want is for <>#ref! to work in my formulas, but for some reason it doesn't a lot of the time.
Thanks.

 

[GDH]

Try the following - I don't know in which cells you have your data so you will need to replace the descriptions below with actual references:

=IF(ISBLANK(cell where date is entered),"", current formula)

This will force the cell the to be blank if a date is yet to be populated.

 

[snipescc]

The problem isn't that I have ref errors. They arn't ideal but I have to have them there if the sheet is going to be easy to use. But what I want is for excel to do something like average the values of b375,e375,h375,k375,n375,q375 but ignore any of the cells that have a #ref! error in them.

 

[GDH]

If you have Excel 2010 try using the AGGREGATE function, using "1" as the first argument for average, and "6" as the second argument for "Ignore error values".

 

[pgc01]

Hi

Try:

=AVERAGE(IF(ISERROR(A2:A10),IF(ERROR.TYPE(A2:A10)<>4,A2:A10),A2:A10))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.

 

[snipescc]

OK, I'll try those formulas when I get back to the office tomorrow.

 

[snipescc]

That one worked! Thank you very much.