Hiding a #DIV/0! comment
 

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

Hiding a #DIV/0! comment
 

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

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

"Bob Smith" wrote:

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

Hiding a #DIV/0! comment
 

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,ROUND DOWN(AVERAGE(K40:K45),0))

"Bob Smith" wrote:

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

Hiding a #DIV/0! comment
 

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

Bob

"E-Coder" wrote in message
...
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,ROUND DOWN(AVERAGE(K40:K45),0))

"Bob Smith" wrote:

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

Hiding a #DIV/0! comment
 

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" wrote in message
ink.net...
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

Hiding a #DIV/0! comment
 

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" wrote in message
...
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" wrote in message
ink.net...
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

Hiding a #DIV/0! comment
 

You might be able to use this one:

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

Biff

"Bob Smith" wrote in message
ink.net...
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" wrote in message
...
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" wrote in message
ink.net...
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

Hiding a #DIV/0! comment
 

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 copypaste
specialformat

any error messages won't be visible.

"E-Coder" wrote:

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

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

"Bob Smith" wrote:

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