make a cell show blank or empty instead of #DIV/0! 2

[DougP]

I have a formula
(M10*385)/(0.00785*1000*L10) which works great.
But it shows #DIV/0! is cell M10 is empty.
this spreadsheet is printed as a report so it needs to look pretty.
I started making an IFF statement but now it shows #NAME?
=iff(M10=0,,(M10*385)/(0.00785*1000*L10))
boths cells M10 and L10 will be empty if not used.

TIA

DougP, MCP, A+

 

[combo]

You can switch off printing errors (page setup options).

combo

 

[SkipVought]

Hi,

You have several options. You might just use the Conditional Format to change the FONT color to the same color as the INTERIOR of the cell.

You could use a formula like =if(L10=0,"",(M10*385)/(0.00785*1000*L10)), but I don't like having "" in cells in a NUMERIC column. Instead I'd use =if(L10=0,0,(M10*385)/(0.00785*1000*L10)) and use CF to make the ZEROS disappear.

Skip,
[sub]
[red][/red]
[/sub]

 

[DougP]

What’s CF? (Cell formatting)
How do I shut off zero specifically?


DougP, MCP, A+

 

[SkipVought]

Conditional Format

Skip,
[sub]
[red][/red]
[/sub]

 

[DougP]

Ok so what do I do exactly to make the zeros not show in the cell, Specifically, exactly. Step by step.

DougP, MCP, A+

 

[DougP]

I just changed the formula to this
=if(L10=0,"",(M10*385)/(0.00785*1000*L10)) as shown above

DougP, MCP, A+

 

[GeekGirlau]

If you don't want zeros to appear, select Format | Cell | Number. Change Category to "Custom" and enter your format.

The way the number formats work is you specify a format for positive numbers, a format for negative numbers, and a format for zero (there's probably more, but this covers the basics). So for "1,000" your format might be "#,##0;-#,##0;". If you leave the format for zeros as blank, they won't be displayed. The other option is to format zeros as a hyphen, such as "#,##0;-#,##0;-".

 

[BlueHorizon]

Hi,

Imagine the example below begins in A1.

The following IF formula placed in C2 will divide A2 by B2 but if the Divisor is a zero, it replaces the error message with a zero.

=IF(A2=0,0,A2/B2)

Qty Multiplier Formula
12 0 0

Hope this helps!


Best,
Blue Horizon

http://www.bluehorizontraining.com

 

[GlennUK]

Blue Horizon ... er, the IF in your example is not checking the divisor ... that's B2, and you're checking A2.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[StrikeEagleII]

There is a much easier way than all of the above discussion--use the iserror() function. It could be implemented like this:

=if(iserror((M10*385)/(0.00785*1000*L10)),"",(M10*385)/(0.00785*1000*L10))

So if iserror() returns true (i.e. the formula produces an error), the cell value is set to "" (which you could change that to whatever you like). If iserror() is false then the formula is executed as normal.

Jason

 

[BlueHorizon]

Thanks, GlennUK, for clarifying.
Tried to give an example by making it clearer than what my spreadsheet actually had but made it cloudier instead. Oh well...I tried.


Best,
Blue Horizon

http://www.bluehorizontraining.com

 

[KenWright]

Strike - there is absolutely no reason to use an additional function when a simple interrogation of the divisor to see it is 0 will do, just as has already been proposed by Skip.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------