Format Near Zero Numbers as Zero ($-)

[Goodwin]

I was hoping to create a custom number format that formatted all near zero numbers (say, any number greater than -0.001 and less than 0.001) to zero. I cannot use the round function because doing so creates other issues further along in the spreadsheet. Does anyone know if this is possible? I tried educating myself on adding conditions to custom number formats but to no avail...

Thanks in advance everyone.

 

[Scott Huish]

I don't think you can do it with a number format because it either requires an ABS function or an AND, neither do I believe you can do with a number format.

Now it could be done with VBA, just need to know a few things. Are the numbers to be formatted typed in or the result of formulas and in what range?

 

[Peter_SSs]

Does this do what you want?

[>=0.0009]General;[<=-0.0009]-General;0

Here's some numbers formatted that way with their underlying values in col B.

Excel Workbook
	A	B
9	0.002	0.002
10	0.0017	0.0017
11	0.0014	0.0014
12	0.0011	0.0011
13	0	0.0008
14	0	0.0005
15	0	0.0002
16	0	-1E-04
17	0	-0.0004
18	0	-0.0007
19	-0.001	-0.001
20	-0.0013	-0.0013
21	-0.0016	-0.0016
Format near 0 to 0

 

[Goodwin]

I don't think you can do it with a number format because it either requires an ABS function or an AND, neither do I believe you can do with a number format.

Now it could be done with VBA, just need to know a few things. Are the numbers to be formatted typed in or the result of formulas and in what range?

The numbers to be formatted are the result of formulas...I had already tried using ABS and AND in the custom number formatting and was hoping my inability to get it to work was a result of the way I was entering the custom number format. Thanks for confirming that I can't take that approach.

 

[Goodwin]

Does this do what you want?

[>=0.0009]General;[<=-0.0009]-General;0

Here's some numbers formatted that way with their underlying values in col B.

Excel Workbook
	A	B
9	0.002	0.002
10	0.0017	0.0017
11	0.0014	0.0014
12	0.0011	0.0011
13	0	0.0008
14	0	0.0005
15	0	0.0002
16	0	-1E-04
17	0	-0.0004
18	0	-0.0007
19	-0.001	-0.001
20	-0.0013	-0.0013
21	-0.0016	-0.0016
Format near 0 to 0

Oh, so close! I should have been more specific in my original post...I'm trying to use a modified Accounting format, so that my near zero numbers look like "$ - ". Your suggestion works great for positive numbers, but with negative numbers it keeps the negative sign in front of the $ sign. Any suggestions? Thanks a ton.

 

[Peter_SSs]

Oh, so close! I should have been more specific in my original post...I'm trying to use a modified Accounting format, so that my near zero numbers look like "$ - ". Your suggestion works great for positive numbers, but with negative numbers it keeps the negative sign in front of the $ sign.

I don't think this can be achieved with Accounting format - at least I can't find a way.

Any suggestions?

Yes, use an extra column. Leave your current formula as is, but hide the column. Create a new column with a formula that sets your current formula values that are 'near' to zero, equal to zero. Format this column with the standard Accounting format. Continue to use your current formula results (in hidden column) for your further calculations.

Edit: The Accounting format does not sow very neatly in the Excel jeanie shot below, but I'm sure you get the drift.

Excel Workbook
	A	B	C	D	E
1	Data 1	Data 2	Hidden	Visible	Further calc based on hidden col
2	5	3	2	$ 2.00	$ 1,000.00
3	5	4.5	0.5	$ 0.50	$ 850.00
4	5	4.9996	0.0004	$ -	$ 800.04
5	5	8	-3	$ (3.00)	$ 500.00
6	5	5.0001	-1E-04	$ -	$ 799.99
7	5	5.01	-0.01	$ (0.01)	$ 799.00
Show near 0 as 0

 

[Goodwin]

I don't think this can be achieved with Accounting format - at least I can't find a way.

Neither could I...oh well. Thanks for the suggestion of the hidden column. I was trying to avoid "unnecessary" columns for fear of confusing the ultimate user, but it looks like that's my only option. Thanks for all your help Peter.