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;

=IF(B1>500,ROUND((B1/1000),0),IF(B1<-500,ROUND((B1/1000),0),-))

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!