Custom accounting format

D

Dallman Ross

I'd like to massage the Accounting format for numbers
so that the font color is blue if the number is > 365.

I have something like that for a custom number format,
but I can't seem to figure it out for the Accounting
format when I select Custom and try to change it.

For reference, here's the similar custom one I have for
numbers:

[Blue][>=365]0;General

Thanks for ideas,
-dman-
 
D

Dave O

One possibility: use conditional formatting to change the font color at
that threshold.
 
D

Dallman Ross

Dave O said:
One possibility: use conditional formatting to change the font
color at that threshold.
Click to expand...

Yes, thanks; I'm wanting to do this expressly to save my
conditional format for other things. :)

If you select the Accounting format style and then go to
Custom, you can see the default code (Excel 2002) for the format:

_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)

The default for numbers that turn red when negative
and use parens in that case also is:

0.00_);[Red](0.00)

So at some point I was able to take a format and hack it
so it would be blue at the threshold of 365, in the manner
I posted. Again, that was:

[Blue][>=365]0;General

I've been using that format for a few years.
I don't remember how I figured out the hack, but I
am convinced a web search had something to do with it. :)

I don't understand all the nuances of the syntax. The
Accounting one seems the most complex of these three.
I can hazard a guess that the first statement (of four separated
by semicolons) would be for positive figures; the second
for negative; the third for zero, and -- I don't get the
fourth.

If I try something, e.g.,

[Blue][>365]_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)

it is close. The large numbers do come out blue. But the
other numbers come out with a minus sign in front of them,
and I don't know enough about the syntax to understand why.

-dman-
 
S

sshankle

Sir Ross:

From your other post, I took this as a challenge...

I hacked about a bit and came up with the following format line for you:

[Blue][>365] _* #,##0.00_);[Red][<=-1.2]_(* (#,##0.00);[Green]_(*
#,##0.00_);[Yellow]_(@_)

Apparently the format is
1 Do it Blue if conditional (which must normally default to >= 0)
2 Do it Red if conditional (which must default default to <0)
3 Do it Green if any other number
4 Do it Yellow if not a number

Steven



Dallman Ross said:
Dave O said:
One possibility: use conditional formatting to change the font
color at that threshold.
Click to expand...

Yes, thanks; I'm wanting to do this expressly to save my
conditional format for other things. :)

If you select the Accounting format style and then go to
Custom, you can see the default code (Excel 2002) for the format:

_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)

The default for numbers that turn red when negative
and use parens in that case also is:

0.00_);[Red](0.00)

So at some point I was able to take a format and hack it
so it would be blue at the threshold of 365, in the manner
I posted. Again, that was:

[Blue][>=365]0;General

I've been using that format for a few years.
I don't remember how I figured out the hack, but I
am convinced a web search had something to do with it. :)

I don't understand all the nuances of the syntax. The
Accounting one seems the most complex of these three.
I can hazard a guess that the first statement (of four separated
by semicolons) would be for positive figures; the second
for negative; the third for zero, and -- I don't get the
fourth.

If I try something, e.g.,

[Blue][>365]_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)

it is close. The large numbers do come out blue. But the
other numbers come out with a minus sign in front of them,
and I don't know enough about the syntax to understand why.

-dman-
Click to expand...
 
D

Dallman Ross

sshankle said:
Sir Ross:

From your other post, I took this as a challenge...
Click to expand...

I was hoping somebody would!
I hacked about a bit and came up with the following format line
for you:

[Blue][>365] _* #,##0.00_);[Red][<=-1.2]_(* (#,##0.00);[Green]_(*
#,##0.00_);[Yellow]_(@_)

Apparently the format is
1 Do it Blue if conditional (which must normally default to >= 0)
2 Do it Red if conditional (which must default default to <0)
3 Do it Green if any other number
4 Do it Yellow if not a number
Click to expand...

That's great, and it's just what I needed. I was trying to change
the second statement to get rid of the negative sign, but I should
have been changing the third. I was a bit confused by the idea
of four options for numbers that can only be positive or negative.
But now I see. (Hmm, I wonder if zero is normally considered neither
positive nor negative. I'll have to test that. . . . Yes, that
is the case! Interesting. It also turns out that the "_" char
represents whitespace, which is why the Accounting format is offset
and which offset is why I chose the Accounting format for this
display to begin with.)

That you very much for rising to the gauntlet, Steven! :)
That is most helpful.

-dman-

========================================================================
Dallman Ross said:
Dave O said:
One possibility: use conditional formatting to change the font
color at that threshold.
Click to expand...

Yes, thanks; I'm wanting to do this expressly to save my
conditional format for other things. :)

If you select the Accounting format style and then go to
Custom, you can see the default code (Excel 2002) for the format:

_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)

The default for numbers that turn red when negative
and use parens in that case also is:

0.00_);[Red](0.00)

So at some point I was able to take a format and hack it
so it would be blue at the threshold of 365, in the manner
I posted. Again, that was:

[Blue][>=365]0;General

I've been using that format for a few years.
I don't remember how I figured out the hack, but I
am convinced a web search had something to do with it. :)

I don't understand all the nuances of the syntax. The
Accounting one seems the most complex of these three.
I can hazard a guess that the first statement (of four separated
by semicolons) would be for positive figures; the second
for negative; the third for zero, and -- I don't get the
fourth.

If I try something, e.g.,

[Blue][>365]_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)

it is close. The large numbers do come out blue. But the
other numbers come out with a minus sign in front of them,
and I don't know enough about the syntax to understand why.
Click to expand...
Click to expand...
 
Top