Custom accounting format
 

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-

Custom accounting format
 

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

Custom accounting format
 

In . com, Dave O
spake thusly:

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

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-

Custom accounting format
 

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" wrote:

In . com, Dave O
spake thusly:

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

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-

Custom accounting format
 

In , sshankle
spake thusly:

Sir Ross:

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

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

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" wrote:

In . com, Dave O
spake thusly:

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

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.