Decimal Alignment

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Decimal Alignment

Paul Cohen
Is there any way to format data in a column so that the decimal points
are aligned?


--
*Paul Cohen*
Reply | Threaded
Open this post in threaded view
|

Re: Decimal Alignment

Johnny Rosenberg
Den 2010-12-22 20:13:58 skrev Paul Cohen <[hidden email]>:

> Is there any way to format data in a column so that the decimal points
> are aligned?

I thought that something like ”0,#####” should work, but it didn't… so now  
we are two who want to know…

--
Kind regards

Johnny Rosenberg

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Decimal Alignment

Harold Fuchs-8
On 22/12/2010 19:36, Johnny Rosenberg wrote:
> Den 2010-12-22 20:13:58 skrev Paul Cohen <[hidden email]>:
>
>> Is there any way to format data in a column so that the decimal points
>> are aligned?
>
> I thought that something like ”0,#####” should work, but it didn't… so
> now we are two who want to know…
>
Perhaps you are trying to achieve something I don't understand but, from
your question, I'd say simply format the cells with a fixed number of
decimal places - Format>Cells>>Numbers>Options>Decimal Places - set the
value to, say, 2 and all the decimal points will line up. You can do it
for a whole column by selecting the column, or for a range of cells by
selecting only those cells. With a value of 2 in the Decimal Places
setting a value of 1234 will be shown as 1234.00 and a value 3.4 will
show as 3.40 so that the decimal point appears in the right place.

You can also set the next option in that same pane - "Leading Zeros". If
you set that to zero then the value 0.1 will appear as .10 but the
decimal point will still line up with the others. If you set it to 2
then 0.12 will appear as 00.12 and, again, the decimal point will be in
the right place.

I'm sorry, but this seems so fundamental that I think I must have
misunderstood something.

--
Harold Fuchs
London, England


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Decimal Alignment

Bob Estes-2
In reply to this post by Johnny Rosenberg
On 12/22/2010 11:36 AM, Johnny Rosenberg wrote:
> Den 2010-12-22 20:13:58 skrev Paul Cohen <[hidden email]>:
>
>> Is there any way to format data in a column so that the decimal points
>> are aligned?
>
> I thought that something like ”0,#####” should work, but it didn't… so
> now we are two who want to know…
>

I'm assuming you're working in calc.  If you format the cells with
0.000000 it will display six significant figures to the right of the
decimal.  It will display a zero if there is no other number to display.
  Because each number has the same number of significant figures to the
right of the decimal, the decimal points will be aligned.  Adjust the
format to display the maximum number of significant figures desired.


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Decimal Alignment

Peter Hillier-Brook
In reply to this post by Paul Cohen
On 22/12/2010 19:13, Paul Cohen wrote:
> Is there any way to format data in a column so that the decimal points
> are aligned?

In Calc, Format>Cells Category 'General' Format 'Number' is a good
starting point. This works when the number of digits after the decimal
point are similar in all cells.

In Writer, decimal tabs should do what you want.

Peter HB

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Decimal Alignment

mike scott-2
In reply to this post by Bob Estes-2
On 22/12/2010 20:13, Bob Estes wrote:

> On 12/22/2010 11:36 AM, Johnny Rosenberg wrote:
>> Den 2010-12-22 20:13:58 skrev Paul Cohen <[hidden email]>:
>>
>>> Is there any way to format data in a column so that the decimal points
>>> are aligned?
>>
>> I thought that something like ”0,#####” should work, but it didn't… so
>> now we are two who want to know…
>>
>
> I'm assuming you're working in calc. If you format the cells with
> 0.000000 it will display six significant figures to the right of the
> decimal. It will display a zero if there is no other number to display.
> Because each number has the same number of significant figures to the
> right of the decimal, the decimal points will be aligned. Adjust the

Only for a fixed width font though.



--
Mike Scott
Harlow, Essex, England

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Decimal Alignment

Paul Cohen
On 12/22/2010 4:21 PM, Mike Scott wrote:

> On 22/12/2010 20:13, Bob Estes wrote:
>> On 12/22/2010 11:36 AM, Johnny Rosenberg wrote:
>>> Den 2010-12-22 20:13:58 skrev Paul Cohen <[hidden email]>:
>>>
>>>> Is there any way to format data in a column so that the decimal points
>>>> are aligned?
>>>
>>> I thought that something like ”0,#####” should work, but it didn't… so
>>> now we are two who want to know…
>>>
>>
>> I'm assuming you're working in calc. If you format the cells with
>> 0.000000 it will display six significant figures to the right of the
>> decimal. It will display a zero if there is no other number to display.
>> Because each number has the same number of significant figures to the
>> right of the decimal, the decimal points will be aligned. Adjust the
>
> Only for a fixed width font though.
>
>
>
Yes, that's the kind o kluge I've been using.  What I'd like to see
added to calc is behavior along the lines of the way a decimal tab works
in Writer.

--
*Paul Cohen*
Reply | Threaded
Open this post in threaded view
|

Re: Decimal Alignment

Bob Estes-2
On 12/22/2010 01:28 PM, Paul Cohen wrote:

> On 12/22/2010 4:21 PM, Mike Scott wrote:
>> On 22/12/2010 20:13, Bob Estes wrote:
>>> On 12/22/2010 11:36 AM, Johnny Rosenberg wrote:
>>>> Den 2010-12-22 20:13:58 skrev Paul Cohen <[hidden email]>:
>>>>
>>>>> Is there any way to format data in a column so that the decimal points
>>>>> are aligned?
>>>>
>>>> I thought that something like ”0,#####” should work, but it didn't… so
>>>> now we are two who want to know…
>>>>
>>>
>>> I'm assuming you're working in calc. If you format the cells with
>>> 0.000000 it will display six significant figures to the right of the
>>> decimal. It will display a zero if there is no other number to display.
>>> Because each number has the same number of significant figures to the
>>> right of the decimal, the decimal points will be aligned. Adjust the
>>
>> Only for a fixed width font though.
>>
>>
>>
> Yes, that's the kind o kluge I've been using. What I'd like to see added
> to calc is behavior along the lines of the way a decimal tab works in
> Writer.
>
That would be a nice feature.  If it gets submitted, I'll vote for it.



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Decimal Alignment

Johnny Rosenberg
In reply to this post by Harold Fuchs-8
Den 2010-12-22 21:01:13 skrev Harold Fuchs <[hidden email]>:

> On 22/12/2010 19:36, Johnny Rosenberg wrote:
>> Den 2010-12-22 20:13:58 skrev Paul Cohen <[hidden email]>:
>>
>>> Is there any way to format data in a column so that the decimal points
>>> are aligned?
>>
>> I thought that something like ”0,#####” should work, but it didn't… so  
>> now we are two who want to know…
>>
> Perhaps you are trying to achieve something I don't understand but, from  
> your question, I'd say simply format the cells with a fixed number of  
> decimal places - Format>Cells>>Numbers>Options>Decimal Places - set the  
> value to, say, 2 and all the decimal points will line up. You can do it  
> for a whole column by selecting the column, or for a range of cells by  
> selecting only those cells. With a value of 2 in the Decimal Places  
> setting a value of 1234 will be shown as 1234.00 and a value 3.4 will  
> show as 3.40 so that the decimal point appears in the right place.
>
> You can also set the next option in that same pane - "Leading Zeros". If  
> you set that to zero then the value 0.1 will appear as .10 but the  
> decimal point will still line up with the others. If you set it to 2  
> then 0.12 will appear as 00.12 and, again, the decimal point will be in  
> the right place.
>
> I'm sorry, but this seems so fundamental that I think I must have  
> misunderstood something.
>

You can do this in Writer, as a matter of fact, and I would guess the  
purpose of this is to line numbers up nicely if the number of decimals are  
not the same for all the values, like this (you need to use a font like  
Liberation Mono, Free Mono, Courier or similar for this to look right):
   45.321
    3.9
1325.
2537.99225

Well, you get the point… (hopefully the trailing spaces are not omitted  
somewhere on the way between now when I send the message and later when  
you receive it…). The decimal symbol should be in the same position for  
all the values above.

As I said, in OpenOffice.org Writer you can do this and you can do it for  
any character, for example the letter ”k”:

Resistance (Ω)
   2k49
   1k
  24k9
249k
and so on.

--
Kind regards

Johnny Rosenberg

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Decimal Alignment

Paul Cohen
On 12/24/2010 5:59 AM, Johnny Rosenberg wrote:

> Den 2010-12-22 21:01:13 skrev Harold Fuchs <[hidden email]>:
>
>> On 22/12/2010 19:36, Johnny Rosenberg wrote:
>>> Den 2010-12-22 20:13:58 skrev Paul Cohen <[hidden email]>:
>>>
>>>> Is there any way to format data in a column so that the decimal points
>>>> are aligned?
>>>
>>> I thought that something like ”0,#####” should work, but it didn't…
>>> so now we are two who want to know…
>>>
>> Perhaps you are trying to achieve something I don't understand but,
>> from your question, I'd say simply format the cells with a fixed
>> number of decimal places - Format>Cells>>Numbers>Options>Decimal
>> Places - set the value to, say, 2 and all the decimal points will
>> line up. You can do it for a whole column by selecting the column, or
>> for a range of cells by selecting only those cells. With a value of 2
>> in the Decimal Places setting a value of 1234 will be shown as
>> 1234.00 and a value 3.4 will show as 3.40 so that the decimal point
>> appears in the right place.
>>
>> You can also set the next option in that same pane - "Leading Zeros".
>> If you set that to zero then the value 0.1 will appear as .10 but the
>> decimal point will still line up with the others. If you set it to 2
>> then 0.12 will appear as 00.12 and, again, the decimal point will be
>> in the right place.
>>
>> I'm sorry, but this seems so fundamental that I think I must have
>> misunderstood something.
>>
>
> You can do this in Writer, as a matter of fact, and I would guess the
> purpose of this is to line numbers up nicely if the number of decimals
> are not the same for all the values, like this (you need to use a font
> like Liberation Mono, Free Mono, Courier or similar for this to look
> right):
>   45.321
>    3.9
> 1325.
> 2537.99225
>
> Well, you get the point… (hopefully the trailing spaces are not
> omitted somewhere on the way between now when I send the message and
> later when you receive it…). The decimal symbol should be in the same
> position for all the values above.
>
> As I said, in OpenOffice.org Writer you can do this and you can do it
> for any character, for example the letter ”k”:
>
> Resistance (Ω)
>   2k49
>   1k
>  24k9
> 249k
> and so on.
>
I probably should have been more clear about my question.  What I was
hoping for was a formatting in Calc that would behave much as the
decimal tab works in Writer.  I would imagine selecting a column of
cells and direct that the values, which might not all have the same
numbers of digits, be aligned on their decimal points, just as you would
do when writing numbers in a column on a piece of paper.

--
*Paul Cohen*
Reply | Threaded
Open this post in threaded view
|

Re: Decimal Alignment

Paul Gress-2
In reply to this post by Paul Cohen
On 12/22/10 02:13 PM, Paul Cohen wrote:
Is there any way to format data in a column so that the decimal points are aligned?




There are two ways.  As stated previously, set the number of decimal places and right align.  This would work if a mono space font is used.

The second solution is what I've done (in my invoices I send out).  In the attached sample "decimal_aligned.ods", I enter the dollar value in cell column "A" and the significant value is displayed in cell column "B" without the decimal value and the decimal value in displayed in column "C".  This solution doesn't care about what type of font is used, it can be two different fonts and the decimal will always line up.

Paul

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

decimal_aligned.ods (15K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Decimal Alignment

Harold Fuchs-9
In reply to this post by Johnny Rosenberg
>
>"Johnny Rosenberg" <[hidden email]> wrote in message
>news:op.vn7stngfxqd8ry@pb-laptop...
>Den 2010-12-22 21:01:13 skrev Harold Fuchs <[hidden email]>:
>
>> On 22/12/2010 19:36, Johnny Rosenberg wrote:
>>> Den 2010-12-22 20:13:58 skrev Paul Cohen <[hidden email]>:
>>>
>>>> Is there any way to format data in a column so that the decimal points
>>>> are aligned?
>>>
>>> I thought that something like ”0,#####” should work, but it didn't… so
>>> now we are two who want to know…
>>>
>> Perhaps you are trying to achieve something I don't understand but, from
>> your question, I'd say simply format the cells with a fixed number of
>> decimal places - Format>Cells>>Numbers>Options>Decimal Places - set the
>> value to, say, 2 and all the decimal points will line up. You can do it
>> for a whole column by selecting the column, or for a range of cells by
>> selecting only those cells. With a value of 2 in the Decimal Places
>> setting a value of 1234 will be shown as 1234.00 and a value 3.4 will
>> show as 3.40 so that the decimal point appears in the right place.
>>
>> You can also set the next option in that same pane - "Leading Zeros". If
>> you set that to zero then the value 0.1 will appear as .10 but the
>> decimal point will still line up with the others. If you set it to 2
>> then 0.12 will appear as 00.12 and, again, the decimal point will be in
>> the right place.
>>
>> I'm sorry, but this seems so fundamental that I think I must have
>> misunderstood something.
>>
>
>You can do this in Writer, as a matter of fact, and I would guess the
>purpose of this is to line numbers up nicely if the number of decimals are
>not the same for all the values, like this (you need to use a font like
>Liberation Mono, Free Mono, Courier or similar for this to look right):
>   45.321
>    3.9
>1325.
>2537.99225
>
>Well, you get the point… (hopefully the trailing spaces are not omitted
>somewhere on the way between now when I send the message and later when
>you receive it…). The decimal symbol should be in the same position for
>all the values above.
>
>As I said, in OpenOffice.org Writer you can do this and you can do it for
>any character, for example the letter ”k”:
>
>Resistance (Ω)
>   2k49
>   1k
>  24k9
>249k
>and so on.
>
>--
>Kind regards
>
>Johnny Rosenberg

In that case I haven't misunderstood and the solution I proposed will work.
Calc doesn't use trailing spaces in numbers. In the example you give you'd
need to set 5 decimal places. You'd get
  45.32100
      3.90000
      1325.00000
      2537.99225


Or you could use "Scientific" format which would give
 4.53E+001
      3.90E+000
      1.33E+003
      2.54E+003


or
 4.53E+01
      3.90E+00
      1.33E+03
      2.54E+03


Depending on which option you use.

--
Harold Fuchs
London, England



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Decimal Alignment

Johnny Rosenberg
Den 2010-12-24 19:41:25 skrev Harold Fuchs <[hidden email]>:

>>
>> "Johnny Rosenberg" <[hidden email]> wrote in message  
>> news:op.vn7stngfxqd8ry@pb-laptop...
>> Den 2010-12-22 21:01:13 skrev Harold Fuchs <[hidden email]>:
>>
>>> On 22/12/2010 19:36, Johnny Rosenberg wrote:
>>>> Den 2010-12-22 20:13:58 skrev Paul Cohen <[hidden email]>:
>>>>
>>>>> Is there any way to format data in a column so that the decimal  
>>>>> points
>>>>> are aligned?
>>>>
>>>> I thought that something like ”0,#####” should work, but it didn't…  
>>>> so now we are two who want to know…
>>>>
>>> Perhaps you are trying to achieve something I don't understand but,  
>>> from your question, I'd say simply format the cells with a fixed  
>>> number of decimal places - Format>Cells>>Numbers>Options>Decimal  
>>> Places - set the value to, say, 2 and all the decimal points will line  
>>> up. You can do it for a whole column by selecting the column, or for a  
>>> range of cells by selecting only those cells. With a value of 2 in the  
>>> Decimal Places setting a value of 1234 will be shown as 1234.00 and a  
>>> value 3.4 will show as 3.40 so that the decimal point appears in the  
>>> right place.
>>>
>>> You can also set the next option in that same pane - "Leading Zeros".  
>>> If you set that to zero then the value 0.1 will appear as .10 but the  
>>> decimal point will still line up with the others. If you set it to 2  
>>> then 0.12 will appear as 00.12 and, again, the decimal point will be  
>>> in the right place.
>>>
>>> I'm sorry, but this seems so fundamental that I think I must have  
>>> misunderstood something.
>>>
>>
>> You can do this in Writer, as a matter of fact, and I would guess the  
>> purpose of this is to line numbers up nicely if the number of decimals  
>> are not the same for all the values, like this (you need to use a font  
>> like Liberation Mono, Free Mono, Courier or similar for this to look  
>> right):
>>   45.321
>>    3.9
>> 1325.
>> 2537.99225
>>
>> Well, you get the point… (hopefully the trailing spaces are not omitted  
>> somewhere on the way between now when I send the message and later when  
>> you receive it…). The decimal symbol should be in the same position for  
>> all the values above.
>>
>> As I said, in OpenOffice.org Writer you can do this and you can do it  
>> for any character, for example the letter ”k”:
>>
>> Resistance (Ω)
>>   2k49
>>   1k
>>  24k9
>> 249k
>> and so on.
>>
>> -- Kind regards
>>
>> Johnny Rosenberg
>
> In that case I haven't misunderstood and the solution I proposed will  
> work. Calc doesn't use trailing spaces in numbers. In the example you  
> give you'd need to set 5 decimal places. You'd get
>   45.32100
>       3.90000
>       1325.00000
>       2537.99225

I would guess the zeroes are unwanted in this case – 45.32100 should  
probably rather be 45.321. There is one significant difference between  
them: ”45.32100” suggests that the accuracy is five decimals, while the  
real accuracy probably is only three decimals. This is very important in  
some cases.

--
Kind regards

Johnny Rosenberg

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Decimal Alignment

Michael Adams-2
In reply to this post by Paul Gress-2
On Saturday 25 December 2010 06:35, Paul Gress wrote:
> On 12/22/10 02:13 PM, Paul Cohen wrote:
> > Is there any way to format data in a column so that the decimal points
> > are aligned?
>
> There are two ways.  As stated previously, set the number of decimal places
> and right align.  This would work if a mono space font is used.

Mono-spaced fonts are not necesary. Generally all fonts use equal width for
digits. Test and see.

--
Michael

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Decimal Alignment

Johnny Rosenberg
Den 2010-12-24 22:12:48 skrev Michael Adams <[hidden email]>:

> On Saturday 25 December 2010 06:35, Paul Gress wrote:
>> On 12/22/10 02:13 PM, Paul Cohen wrote:
>> > Is there any way to format data in a column so that the decimal points
>> > are aligned?
>>
>> There are two ways.  As stated previously, set the number of decimal  
>> places
>> and right align.  This would work if a mono space font is used.
>
> Mono-spaced fonts are not necesary. Generally all fonts use equal width  
> for
> digits. Test and see.
>

I have tested that so many times, but just for you I did it again now:
Dejavu Sans – Yes, works fine, but the font is very ugly in my opinion…
Free Sans – Works fine too, looks much better than Dejavu too.
Liberation Sans – ”1” 8 times has approximately the same width as ”2” 7  
times.
Arial – ”1” 8 times has approximately the same width as ”2” 7 times.

So avoid Arial and Liberation Sans (and probably some more fonts) and  
you'll be fine…

I didn't try any Serif fonts, since I hate them anyway.
--
Kind regards

Johnny Rosenberg

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Decimal Alignment

twayne at twaynesdomain
In reply to this post by Paul Gress-2
In news:[hidden email],
Paul Gress <[hidden email]> typed:
> On 12/22/10 02:13 PM, Paul Cohen wrote:
> Is there any way to format data in a column so that the
> decimal points are aligned?

Yes, there is. See Help and search for Tabs, then under that, Settings. It
tells you there how to set a decimal tab stop.
Excerpt:

Decimal
Aligns the decimal point of a number to the center of the tab stop and text
to the left of the tab.

The character that is used as a decimal separator depends on the regional
setting of your operating system.

You gave no details, so I assumed OO.o V3.x on XP to check out the Help and
assumed a Writer document since you gave no hint otherwise. So yes, it can
do it. Then I played with it, as I've done before, and it works as expected.

  The advice to simply right-align the numbers doesn't work unless ALL data
to format has the same number of digits to the right of the decimal. This
Decimal Tab works directly on the decimal and doesn't care how many digits
are before/after the decimal.

Also please do not post in HTML mode: Use Plain Text instead for all
newsgroups unless they specifically say otherwise. There are still a lot of
good newsreaders around that will only display garbage if you use the HTML
mode. If you don't know how to do that, see your news client's Help for
assistance.

HTH,

Twayne`


>
>
>
>
> There are two ways.  As stated previously, set the number
> of decimal places and right align.  This would work if a
> mono space font is used.
>
> The second solution is what I've done (in my invoices I
> send out).  In the attached sample "decimal_aligned.ods", I
> enter the dollar value in cell column "A" and the
> significant value is displayed in cell column "B" without
> the decimal value and the decimal value in displayed in
> column "C".  This solution doesn't care about what type of
> font is used, it can be two different fonts and the decimal
> will always line up.
>
> Paul
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]




---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Decimal Alignment

Niklas Nebel-2
In reply to this post by Bob Estes-2
On 12/22/10 23:02, Bob Estes wrote:
> On 12/22/2010 01:28 PM, Paul Cohen wrote:
>> Yes, that's the kind o kluge I've been using. What I'd like to see added
>> to calc is behavior along the lines of the way a decimal tab works in
>> Writer.
>>
> That would be a nice feature. If it gets submitted, I'll vote for it.

There is an old issue (16819), but it's not a feature that is frequently
requested. The usual way to align numbers in spreadsheets is a fixed
number of decimals, as you described.

Niklas

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]