×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Number format in worksheet
2

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.

RE: Number format in worksheet

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

RE: Number format in worksheet

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: Eng-Tips.com Forum Policies

RE: Number format in worksheet

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.

 

RE: Number format in worksheet

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: Eng-Tips.com Forum Policies


RE: Number format in worksheet

(OP)
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:

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

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

good luck

RE: Number format in worksheet

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: Eng-Tips.com Forum Policies


RE: Number format in worksheet

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

RE: Number format in worksheet

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.

 

RE: Number format in worksheet

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources