Issue: Custom Number Format to Display (thousands or dashes)

**Crawfinator1** · 03-18-2013, 11:46 PM

I need a custom format to display numbers in thousands, but that if the result is zero or rounds down or up to zero it is displayed as a dash. Oh and negatives need to be displayed in (parenthases).....

I currently have the following - [>=500]#,##0,;[<=-500](#,##0,);- which works perfectly other than for negatives that are between 0 and -500. These are displayed with a double dash i.e. --.

So as an example the above format rule does the following.

0 = - (correct)
400 = - (correct)
550 = 1 (correct)
5550 = 6 (correct)
55550 = 56 (correct)
555550 = 556 (correct)
5555550 = 5,556 (correct)
-400 = -- (incorrect)
-550 = (1) (correct)
-5550 = (6) (correct)
-55550 = (56) (correct)
-555550 = (556) (correct)

and so on etc.....

Can anyone help???

**swhitesell** · 03-20-2013, 06:19 PM

Following this from your duplicate post. I do not believe you will not be able to solve your problem with a custom number format. A solution is to instead create another column with a nest if statement. If your value is in Column B try this;

Please Login or Register  to view this content.

Then apply the Accounting number format with no decimals to the result.

The reason custom format won't work is it is limited to three criteria.

**JosephP** · 03-21-2013, 03:34 AM

untested but perhaps
[>=500]#,##0,;[<=-500](#,##0,);"-";@

Edit: nope that fails too. I think you'd have to use conditional formatting or a formula as already suggested

**aydeegee** · 03-24-2013, 09:24 AM

erm ... perhaps we have over complicated things?

I have found the following Custom format cells seems to work:

#,##0_);(#,##0);-_)
together with (in column B) =ROUND(A1/1000,0)

Have I missed something?

Regards
Alastair

**JosephP** · 03-24-2013, 10:50 AM

well the op only asked for a custom format not a formula and a custom format but that was already suggested ;-)

**Crawfinator1** · 03-24-2013, 06:59 PM

I need the format for a number of largish tables with financial figures so unfortunately it is impracticle to include reference collumns... Part of the reason i wanted to do this was to get rid of the need for excessive rounding formulas etc.

However! You have both put me on to something that works!

Basically i have done the following.....

1. Applied custom format to all cells #,##0,;(#,##0,); This rounds all numbers to the thousand and puts negatives with parenthesis..... Then
2. Applied a conditional format to all cells that formats any cell between 500 & -500 with the number format "-";"-";"-";@

Works perfectly!