Number format in worksheet
Number format in worksheet
(OP)
Other than selecting the entire spreadsheet and doing a cell-format on it, is there a way (say in Options) to pre-set cells to a set number of significant digits?
Right now, if I enter a value in a cell, such as =33.75/1.254 I get the value: 26.9138756. I don't want all my numbers set to 7 significant digits. I want to show it at 26.91.
Just wondering if there is a way to preset this default to 2 instead of 7.
Right now, if I enter a value in a cell, such as =33.75/1.254 I get the value: 26.9138756. I don't want all my numbers set to 7 significant digits. I want to show it at 26.91.
Just wondering if there is a way to preset this default to 2 instead of 7.





RE: Number format in worksheet
For example:
the number 12345.6 has 6 significant digits but displays only one decimal place. The number .000000123 has only 3 significant digits but has 9 decimal places.
If you change the number of decimal places displyaed to some default value, such as 2, you may truncate values less than .005. If you're sure that's what you want to do, OK, but you may be creating problems you don't need to create.
good luck
RE: Number format in worksheet
Hg
Eng-Tips policies: FAQ731-376: Eng-Tips.com Forum Policies
RE: Number format in worksheet
Back to JAE's original question:
He says "I want to show it at 26.91." I interpret this to mean that he wants to display (show) only four significant digits or he wants to display (show) only two decimal places. My point was, and is, that significant digits and decimal places are NOT synonymous.
I said nothing, and know nothing, about how Excel, or any other spreadsheet, uses numbers to do calculations. Some programs, ACAD for example, allow the user to set the number of decimal places used so as to reduce the time and memory needed to do such calculations.
So, the original question seems to me, at least, to be ambiguous. If all JAE wants to do is control how numbers are displayed he can use the "round" function. But if he wants to control how the spreadsheet uses the numbers he enters then he may, or may not, be able to do so. I don't know. If you know the answers to his questions, please inform us all.
RE: Number format in worksheet
RE: Number format in worksheet
I think that you'd need a macro, something like:
Private Sub Workbook_Open()
Cells.Select
Selection.NumberFormat = "0.00"
End Sub
This is quite brute-force, but appears to work.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Number format in worksheet
My fault - shouldn't have used the term "significant digits" as that just muddied up the question.
When I enter something like =135/1.2471 - my Excel spreadsheet shows a number like this:
108.2511427
This is 7 digits past the decimal.
If I enter this: =257/56681995
I get this: 45.34067652
which is 8 digits past the decimal.
The first one has 7 digits past the decimal. The second one, for some reason, shows 8.
My question is: How does Excel choose this degree of precision and can I control it up front for the whole worksheet?
RE: Number format in worksheet
good luck
RE: Number format in worksheet
Here is a link to information on styles: http://o
Here is a link to information on saving styles to use in all new workbooks: http://
RE: Number format in worksheet
The auto_open macro will work for any supported format, including the floating point ones and printing of the trailing zeros.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Number format in worksheet
Just be careful what you are doing and what you are displaying.
Even though you are are dealing with MS Excel, old computer programming rules still aply.
Thanks,
Gordan Feric, PE
Engineering Software
http://members.aol.com/engware
RE: Number format in worksheet
108.2511427 shows up with 7 digits after the decimal and 45.34067652 with 8 because Excel puts as many as will fit in the column width with General number formatting.
RE: Number format in worksheet
The article says you just have to save the book.xlt to C:\Program Files\Microsoft Office\OFFICE11\XLSTART. However I had to save it as something else (book1.xlt) and then use windows explorer to rename the file.
In addition to changing the default number type, my template has the first row automatically wrap text.