Number Format not lining up

[Conan Kelly]

Hello all,

(XL & AC 2003)

I copied data from an AC query, pasted it into a blank XL sheet. I set all
of the values to this number format:

_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
(the standard Accounting format, I think)

Now correct me if I'm wrong, but isn't this number format supposed to take
into account the parens and space positive numbers so the digits & decimal
places will line up? In other words, "0.00" should be spaced out from the
right edge of the cell the width of one closing paren, thus the zeroes &
decimal point in "0.00" should line up with the zeros & decimal point in
"(0.00)".

After applying this format, the positive numbers are not spaced out from the
right edge of the cell.

Has pasting data from AC somehow messed up the formats/alignment/some other
setting?

Thanks for any help anyone can provide,

Conan Kelly

 

[Fred Smith]

You are correct, this format lines up the decimal places for both positive and
negative numbers.

However, your format is missing dollar signs for the standard accounting format,
which I believe is your problem. Here's the copy from Format>Custom:

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

 

[Conan Kelly]

Fred Smith,

Thank you for the feed back, but currency symbols is not my concern. I
don't care if they show up or not........I'd rather they didn't.

What I'm concerned about is "0.00" in A1 does not line up with "(0.00)" in
A2 (keep in mind that these are not text values, these are calculated
numeric results of formulas).

"0.00" should show up near the right edge of the cell, space out from the
right edge the width of one closing paren. That is not the case. "0.00" is
not spaced out from the right edge, it is flush up against it. So this
number format is not working correctly. Please re-read my OP.

Thanks again,

Conan

 

[Fred Smith]

Sorry Conan, I confused Accounting with Currency format.

You are right, the whole idea of the "_)" portion of the format is to reserve
space for the closing parenthesis when the number is positive.

Unfortunately, I cannot duplicate your results. When I use the custom accounting
format, I get the decimals lining up. Positive numbers are a parenthesis width
away from the right edge of the cell.


My only suggestion is to recheck the custom format and ensure that "_)" exists
for positive numbers. You could try using the standard currency format ($ on the
toolbar) to see if that lines up the decimals. If some format will line up the
decimals, then it's the custom format you are using. If no format lines up the
decimals, that's a different problem.

I'm using Excel XP if that makes any difference.

 

[Stephen]

Hello all,

(XL & AC 2003)

I copied data from an AC query, pasted it into a blank XL sheet. I set
all of the values to this number format:

_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
(the standard Accounting format, I think)

Now correct me if I'm wrong, but isn't this number format supposed to take
into account the parens and space positive numbers so the digits & decimal
places will line up? In other words, "0.00" should be spaced out from the
right edge of the cell the width of one closing paren, thus the zeroes &
decimal point in "0.00" should line up with the zeros & decimal point in
"(0.00)".

After applying this format, the positive numbers are not spaced out from
the right edge of the cell.

Has pasting data from AC somehow messed up the formats/alignment/some
other setting?

Thanks for any help anyone can provide,

Conan Kelly

Have you checked the format AFTER pasting? If you paste data in from
anywhere (whether it's elsewhere in the spreadsheet or from an external
source) you will overwrite the format. Instead you can use Paste Special >
Values.

The other possibility is that you have pasted text rather than numeric
values. This often seems to happen when bringing data in from an external
source. If the data is text, no amount of formatting will help you. You
would need to convert to numbers first and then apply your accounting
format.

You can check to see if you have text using a formula like =ISTEXT(A1). If
this gives TRUE, you can often convert to numbers as follows. Put the number
1 in a spare cell somewhere - a cell NOT formatted as text. Copy this cell.
Select the area where you data are and use
Edit > Paste Special > Multiply. (You can then delete the number 1 in the
spare cell.)

 

[Conan Kelly]

Stephen,

Thanks for the feedback

Have you checked the format AFTER pasting? If you paste data in from
anywhere (whether it's elsewhere in the spreadsheet or from an external
source) you will overwrite the format. Instead you can use Paste Special >
Values.

The format was applied after pasting......well not even that really. I
originally copied from AC and pasted in XL (3 different AC sources, 3
different XL sheets). The on the middle sheet, I created formulas to
calculate the difference between corresponding cells on sheets 1 & 3. Then
I changed my formatting, after pasting from AC and then changing data to
formulas. The formatting didn't line up after all of this.

The other possibility is that you have pasted text rather than numeric
values. This often seems to happen when bringing data in from an external
source. If the data is text, no amount of formatting will help you. You
would need to convert to numbers first and then apply your accounting
format.

Shouldn't be any concern as they are formulas returning numeric values.

Thanks again for all of your help,

Conan