In engineering, it is very common to use scientific notation that only displays numbers in factors of 10e-6, 10e-3, 10e0, 10e3, 10e6, 10e9, etc. I've seen this called "engineering notation" - it's just a special subset of scientific notation.
It would be great to have an extra formatting option for this.
1.3e5 would be displayed as 130.0e3
2.0e6 would be displayed as 2.0e6 (no change)
3.2e10 would be displayed as 32.0e9
To merge with a lot of same suggestion
OP here. First, glad to see other people agreeing this is a helpful feature.
Second, @JoeU nailed it!
The format ##0.0E+0 absolutely forces the exponent to be a multiple of 3, whether it's positive or negative. See attached and below:
Raw Units Formatted Units Interpretation
0.5 N 500.0E-3 N Five hundred millinewtons
500 N 500.0E+0 N Five hundred Newtons
5000 N 5.0E+3 N Five kilonewtons
50000 N 50.0E+3 N Fifty kilonewtons
There maybe be some confusion in the previous comments because one user typed "###0.0E+0" instead of "##0.0E+0".
Lastly I agree that applying Greek letters is a totally separate topic. Thanks @JoeU!
Just checked after looking at your attached capture, and the 'engineering notation' in your example is the desired format - this appears to be a new addition - so maybe someone listened after all :-)
For reference, the format is in 'Custom' as ##0.0E+0, which is a new format type in my excel 365
now the SI prefixes is another matter, and I think there is a thread already open elsewhere.
Sorry, then I and every body else does not get what you want to achieve.. :-)
Just so we are sure, it is not the Scientific or Engineering notation that you are seeking? It is something else?
##0,0E+0 does _not_ do the trick - the whole point is to manage the quick referencing of scale in groups of 3 - there is no way to achieve this using the stndard exponent notation. You cannot get 100e-3 from this notation, it displays 1e-5. Please read an understand the OP thoroughly before commenting.
standard SI multipliers like m,micro,nano etc. is another subject. I believe there is a post list already open for this somewhere else.
Given this has been open for >4 years , I have no hope whatsoever that anyone with any influence is listening to our conversations...
In my opinion ##0,0E+0 does the trick, however we need a conversion of the E (eg: 123E-3 to 123m) to engineering notation letters. The complete list is here, OBS there is a difference between capital letters and small letters:
123E-15 -> 123 f
123E-12 -> 123 p
123E-9 -> 123 n
123E-6 -> 123 u
123E-3 -> 123 m
123E0 -> 123
123E+3 -> 123 k
123E+6 -> 123 M
123E+9 -> 123 G
123E+12 -> 123 T
Hans Schulze commented
Even better (and simpler for implementation) would be the ability to Custom Format #.###z"A" to convert .00123 to 1.230uA. Special case: if the final number looks like 1234.0 mV, it should be upgraded to 1.234 V, in other words, >999 goes to 1.xxx.
Or just add a Format / Number / Category Engineering :)
Desperately needed feature. Would be even better if the display options included both e-3, e3, e6, e9 and the standard SI prefixes too: m for e-3, k for e3, M for e6 etc.
No, ###0.0E+0 does not meet the requirements.
As far as I can tell, the closest work around right now is using #,##0.00,"E+3" or #,##0.00,,"E+6" , etc. However, this only works for positive powers of 3 (e.g. kilonewtons, megatons,..) and not negative powers (e.g. millimeters)
Supported!!!!! This feature is way over-due
###0.0E+0 does not meet the requirement. It does not allow you to constrain the exponent to multiples of 3 or allow the mantissa to be a variable number of digits from 1 to 3. For instance 50,000 is displayed at 5e4 using ##0.0E+0, but you want 50e3 to be displayed in engineering formats.
This format is useful in engineering disciplines and should be easy enough to code in. It is widely used in CAD tools and other engineering software - excel is outdated in this area.
Does the format ##0.0E+0 meet the requirement?
Kenneth Barber commented
'Twould be cool.