INTELLIGENT WORK FORUMS FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a Computer / IT professional? Join TekTips now!
 Talk With Other Members
 Be Notified Of Responses
To Your Posts
 Keyword Search
 OneClick Access To Your
Favorite Forums
 Automated Signatures
On Your Posts
 Best Of All, It's Free!
*TekTips's functionality depends on members receiving email. By joining you are opting in to receive email.
Posting Guidelines
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.0E9 would indicate the decimal has to move 9 places to the left so 320.0E9 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 E12 = p, Pico 0.000000001 1.0 E9 = n, Nano 0.000001 1.0 E6 = u, Micro 0.001 1.0 E3 = 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 


