Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    California
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Decimal Alignment (2002 - SP2)

    I have a question from a user that I'm having one heck of a time answering!

    What he wants to do is align numbers in a column by the decimal. This is scientific data and he doesn't want false 0s at the end of each figure. EX: 3.905; 3.9; 3.90 These should all retain their EXACT value and line up, but how? Adding 0s to the end indicates a much more accurate reading than he's able to give and therefore, can't imply.

    Any help appreciated!

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimal Alignment (2002 - SP2)

    Tracy,

    try a number format like ???.???

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Decimal Alignment (2002 - SP2)

    <P ID="edit" class=small>(Edited by tony55 on 05-Nov-04 00:45. )</P>Hi Tracy

    Firstly use a custom number format similar to 0.?????
    Secondly set the alignment of the cells to right aligned.

    Added: Just realised this will not work for numbers like 3.90, it will display it as 3.9

  4. #4
    Star Lounger
    Join Date
    Jun 2001
    Location
    California
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimal Alignment (2002 - SP2)

    Thank you both! This works, but if I have a 0 on the end, it drops it off. Anything I can do about that?

  5. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Decimal Alignment (2002 - SP2)

    Excel will have no way of knowing the number of significant digits for numbers that end in 0 so this sort of custom format will always cut them off. I can not think of a solution that does not involve using multiple custom formats.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Decimal Alignment (2002 - SP2)

    If you have a zero at the end and you want to display it, it implies to me that you have a set number of decimal fugures. Then use something like:
    ??0.000
    I prefer to have a zero to the left of the decimal when it is <1, if not desired then use something like:
    ???.000

    It will always give 3 decmals points but align on the decimal point and it can be centered

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimal Alignment (2002 - SP2)

    I handle such significant trailing zeros by using two columns. The first I format as text so that it displays the 'number' exactly as it is entered e.g. 3.90 or 3.900 (or even 03.90 if it were required!). The second column uses =value(cellref) to turn the text into a number, I usually hide this column. I also add simple "if iserror" conditions to suppress any 'numbers' that don't have a value, e.g <3.9. All dependent cells then refer to the hidden column.

    Finally I simply centre the text column, it doesn't give an exact alignment with all the dec. points under each other but it is near enough for my purposes.

    stuck

  8. #8
    New Lounger
    Join Date
    Nov 2004
    Location
    Mercerville, New Jersey, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimal Alignment (2002 - SP2)

    This may not be the most elegant solution, but it does appear to work.

    1) Select a proportional font such as courier.
    2) Set the output cells to be Right Justified.
    3) Set the format of your input values to text so they can be manipulated and appear just as you entered them (As mentioned in one of the other replies.).
    4) Determine the number of decimal places you want to display. For this example, I chose five (5).

    Assuming the input values are in column A and the results will be in column B, place the following formula in the cells in column B:

    =CONCATENATE(LEFT(A1,SEARCH(".",A1)-1),".",RIGHT(A1,LEN(A1)-SEARCH(".",A1)),REPT(" ",5-LEN(RIGHT(A1,LEN(A1)-SEARCH(".",A1)))))

    Rather than let you try to decipher that mess, here's how it works:
    I used CONCATENATE to produce the final result string.
    The first part of the result is the values to the left of the decimal point: LEFT(A1,SEARCH(".",A1)-1)
    Followed by a period: "."
    The next part is the values to the right of the decimal point: RIGHT(A1,LEN(A1)-SEARCH(".",A1))
    The final section appends blanks to the end of the string up to the number of decimal places you chose: REPT(" ",5-LEN(RIGHT(A1,LEN(A1)-SEARCH(".",A1))))

    I did this in Excel 2003. There are a couple of things that I didn

  9. #9
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimal Alignment (2002 - SP2)

    Hmm, interesting but I'm not that desperate to get all my number to decimal align! It may help the original poster though.

    In my case your last points would almost certainly be where it unravels. I deal with analytical chemistry results and there is always somebody, somewhere who doesn't think about the answer they are reporting and blindly reports their answer to a huge number of significant figures. Also, it is possible that someone may report 39.0, implying the can measure to one dec. place, but some else would report just 39 (no dec. point) meaning they results is only good to integer values.

    By the way, welcome to the lounge, it's great fun here.

    stuck

  10. #10
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Decimal Alignment (2002 - SP2)

    1. I like your icon. Are you pressing the "any key"?
    2. Related question on significant digits.

    We have an excel workbook with analytical data which is reported to 3 significant digits; e.g.
    9.47
    10.5
    10.7
    10.2
    9.84
    (I am assuming that a 9.8 = 9.80). When I do an average of the data (via pivot table), is there a way to automatically format the cell(s) to display the right number of significant digits such that it is 0.00 if < 10 and 00.0 if >= 10?

    Thanks.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Decimal Alignment (2002 - SP2)

    Format cells
    Custom
    [<10]0.00;0.0

    Steve

  12. #12
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Decimal Alignment (2002 - SP2)

    Thanks! Worked like a charm. Now if I can just get the reader to understand significant digits.......

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Decimal Alignment (2002 - SP2)

    Here is a page about Maintaining Accuracy of Significant Digits from Allen Wyatt's Excel Tips.

    Here is "little discussion" of Thoughts and Ideas on Significant Digits in Excel on the same pages.

    I am not sure how "accurate" all the info is, but I agree with all that is written in it. The author is a just "a PhD chemist" who sometimes also posts answers on this board as "sdckapr" <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Steve

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Decimal Alignment (2002 - SP2)

    Sounds vaguely familiar <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Nice article!

  15. #15
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimal Alignment (2002 - SP2)

    1. Sorry no, can't help you there, I haven't got an "any key" either, keep looking. By the way, rest easy, no keyboards were harmed in the making of my userpic.
    2. I see a greater mind than mine (sdckapr) has sorted your XL problem.

    (not) stuck (at the moment)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •