Excel 2003 Hiding #DIV/0 in Data/Subtotal/Avg result

[Karl Perry]

I have a worksheet organized as:
WeekDay / WoodShop / Spa / Pool
Monday / / 1 / 4
Tuesday/ / 2 / 2
Wednesday/ / 1 / 4

I used the Data/Subtotal/Average functionality to determine the average
number of participants in each of the events over the period of time, and
the Woodshop average's cell displays "#DIV/0".

I understand that dividing 3 by 0 is an error, but I want Excel just to
leave the cell blank.

I've tried conditional formatting using the Is Formula / =iserror as
suggested in Help, but Excel is ignoring this.

Is there any way to make Excel do what I want?

TIA,

 

[Biff]

I've tried conditional formatting using the Is Formula / =iserror as

suggested in Help, but Excel is ignoring this.

Assume the #DIV/0! error is in cell A1.

Select cell A1
Goto Format>Conditional Formatting
Formula Is: =ISERROR(A1)
Click the Format button
Set the font color to be the same as the background color.
OK out

Biff

 

[Karl Perry]

Assume the #DIV/0! error is in cell A1.

Select cell A1
Goto Format>Conditional Formatting
Formula Is: =ISERROR(A1)
Click the Format button
Set the font color to be the same as the background color.
OK out

Biff,

OK. I was missing the cell address in the formula. Thanks for that.

Unfortunately this leaves me with dozens or hundreds of cells all of which
will contain this "#DIV/0!" error message. What I'd like to do is highlight
every cell containing the summary data and apply that conditional formatting
formula to every cell at once. Is there a way to indicate, when entering a
formula, "Look at the current cell"? I.e.: =IsError(CURRENT_CELL) ? Again,
I've looked at help but must not be typing the correct keywords.

Or ... must I write a macro for this?

TIA

 

[Biff]

If you select all the cells, the cell reference will automatically adjust.

For example, assume the range of cells is A1:A10.
Select that range, use the same formula and the cell references will
automatically adjust.

Biff

 

[Karl Perry]

If you select all the cells, the cell reference will automatically adjust.

OK. I had tried that but with using the entire data range:
=IsError(A1:A10). It did work with a few cells, but not with all because it
changed the range to cells not within the data range (relative addressing).
I just tried again, but only put the top left cell of the range in the
formula - and that finally did work.

Thanks.