# Thread: Decimal Alignment (2002 - SP2)

1. ## 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. ## Re: Decimal Alignment (2002 - SP2)

Tracy,

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

3. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Decimal Alignment (2002 - SP2)

Format cells
Custom
[<10]0.00;0.0

Steve

12. ## Re: Decimal Alignment (2002 - SP2)

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

13. ## 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. ## Re: Decimal Alignment (2002 - SP2)

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

Nice article!

15. ## 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
•