Replace Error with a Dash
 

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

Replace Error with a Dash
 

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

Replace Error with a Dash
 

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

Replace Error with a Dash
 

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