Hiding a #DIV/0! comment

[Bob Smith]

Hi folks, within another workbook, I track game data for our team. I have a
few columns where games have yet to be played and am showing the following
above error message as there is no data in the columns. Formula I'm using
is - =ROUNDDOWN(AVERAGE(K40:K45),0) .

Any suggestions on modifications so that I don't show the error message,
other than just changing the font to white font?

TIA,

Bob

 

[E-Coder]

You can trap the error and have Excel show nothing instead...

=IF(ISERROR(ROUNDDOWN(AVERAGE(K40:K45),0)),"",ROUNDDOWN(AVERAGE(K40:K45),0))

 

[E-Coder]

The previous formula will make the cell text or "". If you need it to be a
zero use...

=IF(ISERROR(ROUNDDOWN(AVERAGE(K40:K45),0)),0,ROUNDDOWN(AVERAGE(K40:K45),0))

 

[Bob Smith]

Works like a charm E-Coder ... TYVM!

Bob

The previous formula will make the cell text or "". If you need it to be a
zero use...

=IF(ISERROR(ROUNDDOWN(AVERAGE(K40:K45),0)),0,ROUNDDOWN(AVERAGE(K40:K45),0))

 

[T. Valko]

Try this:

=ROUNDDOWN(IF(COUNT(K40:K45),AVERAGE(K40:K45),0),0)

Returns 0 if the range doesn't contain any numbers.

Biff

 

[Bob Smith]

Thanks Biff, that works just as well as the other formula I received. Seems
yours is a bit cleaner (less code). I like less code .

Regards,

Bob

 

[T. Valko]

You might be able to use this one:

=INT(IF(COUNT(K40:K45),AVERAGE(K40:K45)))

Biff

 

[msmischief]

you can simply hide the result by using a conditional format under format.
this saves you having to amend your original formula:

1. Select the cell that contains the error value (e.g. B2).
2. Select 'Format' > 'Conditional formatting'
3. From drop down box select 'Formula Is' and enter following formula;
=iserror(B2)
4. Select 'Format...' tab
5. Format the font colour to white and click ok (x2)
6. Copy the conditional format to the relevant cells by copy>paste
special>format

any error messages won't be visible.