Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
LurfysMa
 
Posts: n/a
Default Numbers won't right-align

I have a fairly simple spreadsheet with 30-40 calculatrions.

Some of the results appear to have a space on the right hand end. Most
of these are calculations, but some are literal numbers. Some are
formatted as currency and some as numbers.

The right justify button doesn't help.

I have checked all of the formatting parameters I can think of.

Can anyone suggest what is going on here?

Thanks

--
  #2   Report Post  
Jon Quixley
 
Posts: n/a
Default


CURRENCY format places a slight indent on the right hand end of a
number. The NUMBER format does the same thing. GENERAL format shoves
the number hard up against the right hand end, but it is possible to
move this using the INDENT option under the ALIGNMENT tab under the
FORMAT option for that cell.
I'm not sure which result you're after, but if you have currency
formats in there, then it sounds as if you'd prefer everything to have
the slight indent. If this is the case, then format everything else to
NUMBER and they should line up

Cheers
Jon


--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=398492

  #3   Report Post  
LurfysMa
 
Posts: n/a
Default

On Wed, 24 Aug 2005 04:59:59 -0500, Jon Quixley
wrote:

CURRENCY format places a slight indent on the right hand end of a
number. The NUMBER format does the same thing. GENERAL format shoves
the number hard up against the right hand end, but it is possible to
move this using the INDENT option under the ALIGNMENT tab under the
FORMAT option for that cell.


All of the cells are formatted with either Currency or Number. None of
them are General. Most of the Currency-formatted cells are indented,
but some are flush right. Most of the Number-formatted cells are flush
right, but some are indented. The indenting is consistent across a
row.

Is there a way to get the Currency and Number formats to omit that
right hand space?

I'm not sure which result you're after, but if you have currency
formats in there, then it sounds as if you'd prefer everything to have
the slight indent. If this is the case, then format everything else to
NUMBER and they should line up


I want everything to be flush right -- no extra spaces.

Here is one column of my table. For all cells, the alignment is
"General". I also tried flush right -- same result.

Use a monospace font to see alignment:

1,000 Number, 0 decimal places, comma separator
$20,000 Currency, 0 decimal places, $ symbol
$1,250 Currency, 0 decimal places, $ symbol
2,000 Number, 0 decimal places, comma separator
$2,000 Currency, 0 decimal places, $ symbol
0.2 Number, 1 decimal place, no separator
$6 Currency, 0 decimal places, $ symbol
$3,256 Currency, 0 decimal places, $ symbol
$16,744 Currency, 0 decimal places, $ symbol
$200,928 Currency, 0 decimal places, $ symbol

As you can see, some of the Currency-formatted cells are indented and
some are flush right. I can't find any difference in any of the
formatting settings.

All of the Number-formatted cells are flush right in this table, but
there is another table where a couple of them are indented.

--
  #4   Report Post  
Bernard Liengme
 
Posts: n/a
Default

The Number format has no right space
The Accounting format (what you get with the $ tool) has a right space
The Currency format (Format|Cells|Currency has no right spaces)

Make a cell Accounting with the $ (or your currency tool)
Select it and use Format | Style. Can you see where the space comes from?
Look at _-$* #,##0.00_; its that final underscore that makes the space.
You could modify your Currency tool style to get rid of the spaces in all
three parts of the format specs

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"LurfysMa" wrote in message
...
I have a fairly simple spreadsheet with 30-40 calculatrions.

Some of the results appear to have a space on the right hand end. Most
of these are calculations, but some are literal numbers. Some are
formatted as currency and some as numbers.

The right justify button doesn't help.

I have checked all of the formatting parameters I can think of.

Can anyone suggest what is going on here?

Thanks

--



  #5   Report Post  
LurfysMa
 
Posts: n/a
Default

On Thu, 25 Aug 2005 12:57:57 -0300, "Bernard Liengme"
wrote:

The Number format has no right space


On this spreadsheet, sometimes it does and sometimes it doesn't.
Unless there is something that I don't understand -- always a good
possibility.

The Accounting format (what you get with the $ tool) has a right space
The Currency format (Format|Cells|Currency has no right spaces)


Again, some (cells) do and some don't. It doesn't seem to matter
whether I use "Format | Cells | Currency" or right-click on the
cell(s), select Format Cells, and then select Currency.

Make a cell Accounting with the $ (or your currency tool)
Select it and use Format | Style. Can you see where the space comes from?
Look at _-$* #,##0.00_; its that final underscore that makes the space.


OK. I selected a cell, clicked on the "$" tool, entered "123", and hit
Enter. It formatted as "$123.00".

I then clicked on Format|Style and got the Style panel. The Style name
is "Currency". The Number option is checked and there is a complicated
template with four parts. They are (unless I made a typo):

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

The first 3 have a trailing "_", which is what I think you are talking
about. Right?

As an aside, I thought the formatting had just 3 parts: 0, <0, =0.
What's the 4th part "_(@_)" for?

So that looks like what you are describing.

Now I repeated this on one of the cells in my spreadsheet that is
acting up. When I clicked on Format|Style and get the Style panel, the
Style name is "Normal" and there is no format string as above.

You could modify your Currency tool style to get rid of the spaces in all
three parts of the format specs


How do I do that? I tried the Modify button in the Style panel, but I
couldn't find anywhere to get at the format string.

best wishes


Thanks.

PS: I am using Excel 2000 (SP3) if that makes a difrference.


Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"LurfysMa" wrote in message
.. .
I have a fairly simple spreadsheet with 30-40 calculatrions.

Some of the results appear to have a space on the right hand end. Most
of these are calculations, but some are literal numbers. Some are
formatted as currency and some as numbers.

The right justify button doesn't help.

I have checked all of the formatting parameters I can think of.

Can anyone suggest what is going on here?

Thanks

--




--
Running Excel 2000 SP-3 on Windows 2000


  #6   Report Post  
LurfysMa
 
Posts: n/a
Default

On Tue, 23 Aug 2005 23:14:24 -0700, LurfysMa
wrote:

I have a fairly simple spreadsheet with 30-40 calculatrions.

Some of the results appear to have a space on the right hand end. Most
of these are calculations, but some are literal numbers. Some are
formatted as currency and some as numbers.

The right justify button doesn't help.

I have checked all of the formatting parameters I can think of.

Can anyone suggest what is going on here?

Thanks


I discovered the problem. It really has nothing to do with some format
styles adding a trailing space and others not. It has to do with which
format is selected for negative numbers.

Somehow, some of my cells were changed so that negative numbers were
to be formatted in parentheses "(1234.56)". Since that involves a
character (the closing parenthesis) after the number, the style adds a
trailing space for positive numbers -- I presume so that they will
align in nice columns.

I didn't notice that difference when I was comparing cells that did
have the space with those that didn't.

All I had to do was select the cells with trailing spaces and select
the first negative number option (-1234.56) and all of the trailing
spaces disappeared.

My copy of Excel seems to have the default formatting style for
negative numbers set to (1234.56) for both the Number and the Currency
formats. Is there some way that I can change that default so that when
I select either Number or Currency formatting, negative numbers
default to being formatted as "-1234.56"?

Thanks

I suppose one could argue that


--
Running Excel 2000 SP-3 on Windows 2000
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I align numbers where one number has a dollar sign? Carter Devereaux Charts and Charting in Excel 1 June 26th 05 07:20 PM
Checking Winning Numbers in the Lottery. Ann Excel Discussion (Misc queries) 4 May 18th 05 10:55 AM
comma align numbers bacai Excel Discussion (Misc queries) 3 April 19th 05 10:38 PM
Align $ sign next to numbers Christina Excel Discussion (Misc queries) 5 April 7th 05 07:21 PM
Match Last Occurrence of two numbers and Return Date Sam via OfficeKB.com Excel Worksheet Functions 6 April 5th 05 12:40 PM


All times are GMT +1. The time now is 09:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"