Sorry Rebekah.

I might have thought of another simple solution:

If the values that are supposed to be in the cells are numerical, then set
the default pen colour for that cell the same as the fill colour (for
example, black pen on black background. Then using "Condtional Formatting"
instruct Excel to place any value greater than zero in white 'ink'. In that
way the errors will not show as they will be the same colour as the
background, and any value higjher than 0 will show as white (or whatever
colour you choose.)


"Rebekah" wrote:

Popey, this doesn't work... It gets rid of the green arrow from the cornor
but doesn't remove the #DIV/1! text and replace with a zero or blank cell...

"Popey" wrote:

I don't know if I am reading this right, but you do not want the "#DIV/0!" to
show?

Why not simply select all the errors in that column, open the drop down menu
that should appear immediately to the left of the error, and select "ignore
error"? This will make all those annoying error messages in the selected
column disappear. It's what I do, and it works effectively. Saves all that
messing about with altering/adding complex formulas for the same result.

"Rebekah" wrote:

I have multiple columns of data in another sheet and am using this formula
(entered as an array) to provide an average based on conditions, one column
reads "2a", and another reads "38" etc.

i thought that by by entering the IF formula to include "<0", this would
count all the data and return a zero value for a blank or zero value. (I
certainly hope this is making sense to somebody!!!)

=AVERAGE(IF('Summary Days'!$D$3:$D$65536="2A",(IF('Summary
Days'!$K$3:$K$65536=38,(IF('Summary Days'!$O$3:$O$1500<"0",'Summary
Days'!$O$3:$O$1500))))))

This formula works if in all columns there is a value above zero, but
returns "#DIV/0!" if not. This would be ok if my table wasn't presenting
data for a fixed period....
Please help!!!