Format Cells Custom Forumula?

[Guest]

How do I interpret (#,##0_);[Red](#,##0) or other similar formulas so I can
figure out what is wrong or create a new one for a specific situation? I
looked throughout help but couldn't find anything. I know this one rounds
the value of the formula and spaces it so if it is a negagive it doesn't mess
up the spacing on the page, but I don't know how to read the actual formula
to determine what does what.

Thanks, Mike

 

[JE McGimpsey]

See "About custom number formats" in XL Help.

 

[Guest]

That is not a formula, it is a custom format. I don't know exactly what your
example is doing (I don't know what the _ charecter does in custom formats).
Anyways here is what I know about custom formats.

They are all in this format:
Positive Numbers:Negative Numbers:Zero:Text

For Example Using this format:
0.000;-#;0;"Sales "@

Will display the following numbers like so:
1.23 -> 1.230
7 -> 7.000
-1.23 -> -1.23
-7 -> -7
0 -> 0
Hello World -> Sales Hello World

Use 0 when you want to show trailing (or leading) zeros. Use # when you
don't:
Format: 0.000
1.23 -> 1.230
7 -> 7.000
Format: #.###
1.23 -> 1.23
7 -> 7.
Note the . is still there for 7
Format: 00#
1 -> 001
235 -> 235
2359 -> 2359

To change color of text use [color] before the item:
[Black] [Blue]
[Cyan] [Green]
[Magenta] [Red]
[White] [Yellow]

[Blue]#;[Red]-#;[yellow]0;[black]@

Some charecters will be added:
Format: # lb
34 -> 34 lb (this is still a number for formulas)
But it you should use "" for most things
Format: # "lb is enough to break the rack."
34 -> 34 lb is enough to break the rack.

If you want to line things up use ?
Format: # ???/???
The "/" will be lined up.
#.???
The "." will be lined up.

basically play around with it and then look through the help (Custom Fomart
not Custom Formula). If you need help with a certain format don't be afraid
to ask.