Formula for Changing low number of "-"

J

Janet

Folks
One of my users would like to know if its possible to automatically have
numbers lower than 0.049 be changed so the cell displays a dash " - "
instead. I've tried using autocorrect, but that will only change a specific
number and not every number below 0.049.

Does anyone have any ideas?

Thanks.
 
R

Roger Govier

Hi Janet

You could try Format>Cells>Number>Custom> [<0.05]"-";General

Play about with different combinations to get what you want for the normal
format when not less than 0.05
 
S

Sandy Mann

Would a Custom format of

[<0.05]"-";General

in the potentially affected cells do what you want?

This will not change what held in the cell, it will still have the same
value.

If it is the result of a calculation - say A1*B1 - then use:

=IF(A1*B1<0.05,0,A1*B1)

and format as accounting to get a true zero in the cell.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
Top