INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Best of Excel
How to Format a Cell for Engineering Notation
Posted: 9 Aug 08
I searched high and low for the answer to this question, finding nothing, I have posted the solution here so hopefully others will find it quicker in the future.
Is there a way in Excel to format cells in Engineering Notation?
As it turns out Engineering Notation IS available by selecting Cells Format Custom and using the ##0.0E+0 format option ... to increase the number of decimals just add more zero's ie ##0.00E+0
What and Why should anyone want to use Engineering Notation?
This may be clear to some people but for the everyone else ... Engineering Notation is similar to Scientific Notation in that it has the ability to represent large numbers using numbers we are comfortable with. For example a Hard drive specified as 320GB is actually representing a number that is 320,000,000,000 or 320 billion bytes (lets not go into the K factor here (K representing 1024 bytes)) ... 320G is actually already in Engineering Notation (where G is understood to be GIGA or 9 zero's) 320 * 10 ^ 9 (EXCEL formats this to be 320.0E+9 where E represents 10 ^ 9 ) ... this same number in Scientific notation would be 3.2 * 10 ^ 11 (Excel 3.2E+11)
Basically Scientific Notation ranges from negative 9.99 to plus 9.99, where Engineering Notation ranges from negative 999.99 to plus 999.99 ... the E and or * 10 specifies how many places to the (positive) right or (negative) left the decimal moves ... so 320.0E+9 means we have 320 + 9 more 0's ... because it is positive it means the Zero's are to the right of the decimal. A number like 320.0E-9 would indicate the decimal has to move 9 places to the left so 320.0E-9 would actually be .000000320
Putting large numbers into notation format gives us the benifit of viewing smaller numbers which also make it easier to manipulate (if you have to do any calculations consider multiplying 125,021,481 * 0.5 you can do this with Excel and or a calculator easy enough or in your head as 1.25E8 * .5 = 1.25E8 / 2 = .625E8 = 62.5E6 ). Engineering Notation takes it one step further by making it easier to group numbers into familiar units:
0.000000000001 1.0 E-12 = p, Pico
0.000000001 1.0 E-9 = n, Nano
0.000001 1.0 E-6 = u, Micro
0.001 1.0 E-3 = m, Milli
1.0 1.0 E 0
1000.0 1.0 E 3 = K, Kilo, Thousand
1000000.0 1.0 E 6 = M, Mega, Million
1000000000.0 1.0 E 9 = G, Giga, Billion
1000000000000.0 1.0 E 12 = T, Tera, Trillion
Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum
Join Tek-Tips® Today!
Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.
Here's Why Members Love Tek-Tips Forums:
- Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close