I have a formula calling for an average in a workbook that refers to a cell
group that is sometimes not used. To keep the cells from averaging, I insert
an X. That works fine, but when ALL the cells are X I get that irritating
"You are trying to divide something by 0" message. I understand that and
just don't want it to show up in the cell. Can't think of a way. Any help?

=IF(ISERROR(AVERAGE()),"",AVERAGE())
--
Gary''s Student - gsnu200765


"Patrick" wrote:

I have a formula calling for an average in a workbook that refers to a cell
group that is sometimes not used. To keep the cells from averaging, I insert
an X. That works fine, but when ALL the cells are X I get that irritating
"You are trying to divide something by 0" message. I understand that and
just don't want it to show up in the cell. Can't think of a way. Any help?

Hi Patrick,

You can use a formula like:

=IF(ISERR(AVERAGE(A1:A20)),"",AVERAGE)

or if you are using 2007

=IFERROR(AVERAGE(A1:A20),"")

or if you are only concerned with print the errors then choose the following
command in 2003:

File, Page Setup, Sheet, Cell errors as, Blank.

This last feature is available in 2007 also.

You can replace the "" with 0 or anything else.

You may also be able to use the slightly shorter form in 2003 of:

=IF(SUM(A1:A10)=0,"",AVERAGE(A1:A10))
--
Thanks,
Shane Devenshire


"Patrick" wrote:

I have a formula calling for an average in a workbook that refers to a cell
group that is sometimes not used. To keep the cells from averaging, I insert
an X. That works fine, but when ALL the cells are X I get that irritating
"You are trying to divide something by 0" message. I understand that and
just don't want it to show up in the cell. Can't think of a way. Any help?

Thank you!

"Gary''s Student" wrote:

=IF(ISERROR(AVERAGE()),"",AVERAGE())
--
Gary''s Student - gsnu200765


"Patrick" wrote:

I have a formula calling for an average in a workbook that refers to a cell
group that is sometimes not used. To keep the cells from averaging, I insert
an X. That works fine, but when ALL the cells are X I get that irritating
"You are trying to divide something by 0" message. I understand that and
just don't want it to show up in the cell. Can't think of a way. Any help?

Thank you very much

"ShaneDevenshire" wrote:

Hi Patrick,

You can use a formula like:

=IF(ISERR(AVERAGE(A1:A20)),"",AVERAGE)

or if you are using 2007

=IFERROR(AVERAGE(A1:A20),"")

or if you are only concerned with print the errors then choose the following
command in 2003:

File, Page Setup, Sheet, Cell errors as, Blank.

This last feature is available in 2007 also.

You can replace the "" with 0 or anything else.

You may also be able to use the slightly shorter form in 2003 of:

=IF(SUM(A1:A10)=0,"",AVERAGE(A1:A10))
--
Thanks,
Shane Devenshire


"Patrick" wrote:

I have a formula calling for an average in a workbook that refers to a cell
group that is sometimes not used. To keep the cells from averaging, I insert
an X. That works fine, but when ALL the cells are X I get that irritating
"You are trying to divide something by 0" message. I understand that and
just don't want it to show up in the cell. Can't think of a way. Any help?