Author Topic: Excel scientific number  (Read 1771 times)

0 Members and 1 Guest are viewing this topic.

Offline geggi1Topic starter

  • Frequent Contributor
  • **
  • Posts: 448
Excel scientific number
« on: September 10, 2022, 09:50:08 am »
Hi!
I'm working on a spreadsheet in excel with scientific numbers.
I would like to have the numbers following the SI way of numbering.
The numbers would go like this 1E3; 1E6; 1E9 the same way as Kilo, Mega, Giga.
Now i get these numbers in-between like 1E2; 1E5 and so on and it is a bit confusing.
 

Offline Ian.M

  • Super Contributor
  • ***
  • Posts: 13216
 
The following users thanked this post: T3sl4co1l

Offline jpanhalt

  • Super Contributor
  • ***
  • Posts: 4003
  • Country: us
Re: Excel scientific number
« Reply #2 on: September 10, 2022, 10:36:52 am »
Quote
Source: http://www.mathcentre.ac.uk/resources/uploaded/module1textbooklike.pdf
It is just a convention to put the decimal place after the first digit.

If you are just doing the math, I don't see the confusion.  There should be no confusion between numbers and units.  I don't believe my old version of Excel 2007 will do what you want.

Put another way, let's say you have a table of various things with a column labeled kg/m^3.  If the things range from lead to hydrogen, then the number part will cover quite a range of exponents, but the unit would remain the same.

For example (units = kg/m^3)*:
hydrogen: 8.98E-2
air          :1.20E0
water      :1.00E3
lead        :1.134E4

I would find that less confusing than changing to SI units for each entry.

*Source: https://en.wikipedia.org/wiki/Density
 

Offline Ian.M

  • Super Contributor
  • ***
  • Posts: 13216
Re: Excel scientific number
« Reply #3 on: September 10, 2022, 10:51:14 am »
The ##0.0E+0 custom number format from the link I gave works fine even in the truly antique Excel 97!  Unless Microsoft FUBARed number formatting when they introduced the ribbon UI, it should still work in Excel 2007
 

Offline geggi1Topic starter

  • Frequent Contributor
  • **
  • Posts: 448
Re: Excel scientific number
« Reply #4 on: September 10, 2022, 11:57:40 am »
Thanks for your help this will work for me.
I'm working with Mohm and Gohm in my spreadsheet.
 

Online tooki

  • Super Contributor
  • ***
  • Posts: 13157
  • Country: ch
Re: Excel scientific number
« Reply #5 on: September 10, 2022, 03:05:53 pm »
The ##0.0E+0 custom number format from the link I gave works fine even in the truly antique Excel 97!  Unless Microsoft FUBARed number formatting when they introduced the ribbon UI, it should still work in Excel 2007
(Formatting codes of course have absolutely nothing to do with the Ribbon UI.)
I can verify that the absolutely current version of Excel still has the ##0.0E+0 preset listed, and still works as expected — I used it two days ago.
 

Offline Ian.M

  • Super Contributor
  • ***
  • Posts: 13216
Re: Excel scientific number
« Reply #6 on: September 10, 2022, 03:24:54 pm »
(Formatting codes of course have absolutely nothing to do with the Ribbon UI.)
Yes, but it was the most visible change of the major rewrite that also changed the file format to Office Open XML.   IMHO Microsoft have a tendency to drop little used features during product rewrites.
 

Offline SiliconWizard

  • Super Contributor
  • ***
  • Posts: 15797
  • Country: fr
Re: Excel scientific number
« Reply #7 on: September 11, 2022, 01:11:18 am »
Or maybe don't use Excel. ;D
 

Offline Ian.M

  • Super Contributor
  • ***
  • Posts: 13216
Re: Excel scientific number
« Reply #8 on: September 11, 2022, 01:36:44 am »
Certainly, if you don't already use MS Office, you'd be ill advised to start using it without an <expletive> good reason,  but many of us have years of legacy MS Office files, and unfortunately the alternatives generally have compatibility quirks and limitations which make them a PITA when working with complex macro-driven documents originally written in a MS Office application.  I've been running Office 97 for the last 25 years, am now running it on Win10 and don't anticipate 'upgading' to a current version in the forseeable future.
 

Offline CatalinaWOW

  • Super Contributor
  • ***
  • Posts: 5571
  • Country: us
Re: Excel scientific number
« Reply #9 on: September 11, 2022, 05:02:27 am »
Excel has many faults.  But is the defacto standard in much of the world.  If you are interfacing with large corporation or the government in many countries you will be forced to use it.  Same is true about many universities.  Whatever complaint you have about Excel is probably correct, but get over it and move on. 

The only exception to this is in pure science and engineering applications where Matlab may be used.  And just as with Excel there is an open source almost clone called Octave.  Which just like the Excel clones is not quite perfectly compatible.
 
The following users thanked this post: tooki, newbrain

Online tooki

  • Super Contributor
  • ***
  • Posts: 13157
  • Country: ch
Re: Excel scientific number
« Reply #10 on: September 11, 2022, 05:32:24 pm »
(Formatting codes of course have absolutely nothing to do with the Ribbon UI.)
Yes, but it was the most visible change of the major rewrite that also changed the file format to Office Open XML.   IMHO Microsoft have a tendency to drop little used features during product rewrites.
The Office Open XML formats don’t change the program logic nor the data object model. It’s just a different way of saving the same data. (It’s also why the Office Open XML didn’t lead to the easy compatibility many non-programmers thought it would: reading and writing the binary Office file formats was never the issue. That had been reverse engineered years before MS published the formats. The problem is that for true compatibility, you have to replicate the program functionality and bugs, otherwise you get discrepancies.)
 
The following users thanked this post: eugene

Online EPAIII

  • Super Contributor
  • ***
  • Posts: 1165
  • Country: us
Re: Excel scientific number
« Reply #11 on: September 13, 2022, 10:51:11 am »
I may be a bit behind the times, but as I was taught there is scientific notation which places the decimal point after one and only one significant digit and lets the exponent take on any whole number value and there is engineering notation which uses every third value for the exponents while the decimal point can be after one, two, or three significant digits. The engineering notation is what you are calling SI.

Since I learned both of them, I have no problems with either one and when I need to use one or the other, I take my clue from the context. Frankly, I see it as similar to the use of English and metric. I just know both and don't worry about the small stuff.

As for Excel, I have an older version (2K), but can format the cells with any "SPECIAL" format that I want using a number of numbers, letters, and symbols. For scientific notation the format would look like this:

0.00E+00

And the engineering or SI format code would look like this:

##0.0E+0

Of course, those are only two of many such formats. The manner of creating them is explained in the HELP files. You can even do things like having different format strings for positive and negative numbers, different colors, use the comma instead of the period, add the units, spaces, and many other options. I suspect that the same functionality is still present in the later versions of Excel, with perhaps even more options.

I have created a number of special formats for different purposes. And Excel stores all of them under the title "Special" in the format menu for cells so I only need to create them once.
« Last Edit: September 13, 2022, 10:54:25 am by EPAIII »
Paul A.  -   SE Texas
And if you look REAL close at an analog signal,
You will find that it has discrete steps.
 


Share me

Digg  Facebook  SlashDot  Delicious  Technorati  Twitter  Google  Yahoo
Smf