Number format in worksheet 2

[JAE]

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.

 

[RWF7437]

Careful ! Don't confuse "significant digits" woth the number of decimal places.

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

 

[HgTX]

Wait, RWF7437, changing the number of decimal places displayed won't change the number that Excel uses for calculations.

Hg

Eng-Tips policies: faq731-376

 

[RWF7437]

HgTx,

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.

 

[RWF7437]

http://www.tamu-commerce.edu/itde/documents/formats.pdf

 

[IRstuff]

Excel still retains the "Precision as Displayed" choice in the Calculations tab of Options.

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

 

[JAE]

Thanks for the replies.

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:

[red]108.2511427[/red]
This is 7 digits past the decimal.

If I enter this: =257/56681995
I get this: [red]45.34067652[/red]
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?

 

[RWF7437]

Please visit the URL provided in my last post. I found the explanation there very clear.

good luck

 

[wannabeSE]

Have you tried formatting styles?
Here is a link to information on styles:

http://office.microsoft.com/en-us/excel/HA011101991033.aspx?pid=CL100570551033

Here is a link to information on saving styles to use in all new workbooks:

http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP052024221033&CTT=1&Origin=EC010229861033

 

[IRstuff]

Another option is to use the Fixed decimal selection in the Options. This allows you to enter the entire number string and Excel will stick the decimal place as directed. It's not terribly useful, since we're so used to typing the decimal point ourselves. My ATM does that and it's irritating.

The auto_open macro will work for any supported format, including the floating point ones and printing of the trailing zeros.

TTFN

FAQ731-376

 

[Feric]

Hi there:

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

 

[jghrist]

I think the only way to automatically change the cell format is with a macro like IRstuff's.

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.

 

[bacon4life]

You can change your default template to always have a certain number formatting. See

http://www.meadinkent.co.uk/xlconfigure.htm

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.