Replace Error with a Dash

[Karen]

I have a cell with the following formula:
{=AVERAGE(IF(B4:M4<>0,B4:M4))}
It's producing the infamous #DIV/0! error - When the cell produces this
error, I just want a dash in its place. How can this be done?

Thank you

[Gizmo63]

Wrap it all in an if and test for an error:
=if(iserror(AVERAGE(IF(B4:M4<>0,B4:M4))),"-",AVERAGE(IF(B4:M4<>0,B4:M4)))

Note: when using "-" keep in mind other formulas referring to this cell. If
you're doing a calculation then a "-" will produce an error.
You may be better setting the error value to 0 (zero) and using the
formatting to show a dash.

HTH

Giz

"Karen" wrote:

> I have a cell with the following formula:
> {=AVERAGE(IF(B4:M4<>0,B4:M4))}
> It's producing the infamous #DIV/0! error - When the cell produces this
> error, I just want a dash in its place. How can this be done?
>
> Thank you

[Karen]

Thank you for your help - I used this formula on a cell that should not
produce a zero, it should average the numbers and I'm getting a dash. Do you
know what is wrong?
Thank you

"Gizmo63" wrote:

> Wrap it all in an if and test for an error:
> =if(iserror(AVERAGE(IF(B4:M4<>0,B4:M4))),"-",AVERAGE(IF(B4:M4<>0,B4:M4)))
>
> Note: when using "-" keep in mind other formulas referring to this cell. If
> you're doing a calculation then a "-" will produce an error.
> You may be better setting the error value to 0 (zero) and using the
> formatting to show a dash.
>
> HTH
>
> Giz
>
> "Karen" wrote:
>
> > I have a cell with the following formula:
> > {=AVERAGE(IF(B4:M4<>0,B4:M4))}
> > It's producing the infamous #DIV/0! error - When the cell produces this
> > error, I just want a dash in its place. How can this be done?
> >
> > Thank you

[Karen]

Thank you - It works great!
It wasn't working before because I didn't Ctrl + Shift + Enter
Thanks again

"Gizmo63" wrote:

> Wrap it all in an if and test for an error:
> =if(iserror(AVERAGE(IF(B4:M4<>0,B4:M4))),"-",AVERAGE(IF(B4:M4<>0,B4:M4)))
>
> Note: when using "-" keep in mind other formulas referring to this cell. If
> you're doing a calculation then a "-" will produce an error.
> You may be better setting the error value to 0 (zero) and using the
> formatting to show a dash.
>
> HTH
>
> Giz
>
> "Karen" wrote:
>
> > I have a cell with the following formula:
> > {=AVERAGE(IF(B4:M4<>0,B4:M4))}
> > It's producing the infamous #DIV/0! error - When the cell produces this
> > error, I just want a dash in its place. How can this be done?
> >
> > Thank you