Engineering Notation, Again

[jqavins]

I've searched for how to do engineering notation, and found the same answer in several places (multiple threads here and on other sites) but that answer isn't correct. Formats such as ##0.0E+0 give a fixed number of decimal places in the mantissa, but proper engineering notation adjusts the number of decimal places to keep the number of signicant figures constant. See examples below.

Number​	##0.3E+0​	Engineering Notation (three digits)​
pi()*10^6	3.1E+6	3.14E+6
pi()*10^5	314.2E+3	314E+3
pi()*10^4	31.4E+3	31.4E+3
pi()10^3	3.1E+3	3.14E+3
pi*10^-4	314.2E-6	314E-6

<tbody>
</tbody>

See the difference? In regular scientific notation there are always the same number of significant digits, one before the decimal and as specified after. Engineering notation is also supposed to have a constant number of significant digits.

So can anyone think of a way to do it right?

 

[pgc01]

Hi

Use conditional formatting.

Add a rule for each power of 10

For ex., assuming the max is 9.99 million

Add a rule for greater than or equal to 1,000,000, use format 0.00E+0
Add a rule for greater than or equal to 100,000, use format 000E+0

... etc.

This way you'll always get 3 significant digits with powers of 10^3

 

[SpillerBD]

I don't think there is.
Often a zero is used when the last significant place is reached but it represents a significant place.
Significant number of digits is incorrect.
11 has only two digits and therefor only two significant digits? It is significant to the ones place.
Add 111 to 11 and 122 has three? Its significance is the ones place.

It is very difficult for Excel to provide a method of keeping track of where significance occurs and which math operations move the significant place. I believe to actually do it would require numbers to be stored as text for input so that the number of characters/decimal places could be tracked.
Remember, Excel's origins was in accounting not precision mathematics.

darm spurious digits!

 

[jqavins]

Significant number of digits is incorrect.
11 has only two digits and therefor only two significant digits? It is significant to the ones place.
Add 111 to 11 and 122 has three? Its significance is the ones place.

??? Perhaps Excel documentation uses "significant digits" or "significant figures" in a different way, but in science and math:

• 11 has 2 sig figs.
• 111 and 122 each have three.
• 00123 has three.
• 110, on the other hand, has two.
• 0.0000002 has only one.
• 0.2000 has 4.
• 12.34500 has 7.

The significance isn't a specific position, it's the number of digits that count. Leading zeros never count. Trailing zeros before the decimal do not count, but trailing zeros after the decimal do.

It is very difficult for Excel to provide a method of keeping track of where significance occurs and which math operations move the significant place. I believe to actually do it would require numbers to be stored as text for input so that the number of characters/decimal places could be tracked.

I'm not asking Excel to keep track during a computation, just in the display format.

I'll come up with a conditional format set, and I should only need three rules.

• =MOD(INT(LOG(cell)),3) = 0 --> 0.00E+00
• =MOD(INT(LOG(cell)),3) = 1 --> 00.0E+00
• =MOD(INT(LOG(cell)),3) = 2 --> 000E+00

Of course, that's for three sig figs. More would be easy; fewer would be tricky, but I doubt I'd ever want this format with fewer.

 

[jqavins]

OK, this is darn peculiar. I implemented the conditional formatting, and got a failure, only when the MOD(... formula comes out to 1, and only every other time at that. When the correct exponent is even,6, 0, -6, -12, etc. it comes out just fine, e.g. 31.4E+06. But when the exponent should be odd, -3, 3, 9, etc. I get a leading zero and exponent bumped up, e.g. 03.1E+04 instead of the correct 31.4E+03.

If it always failed for LOG(... = 1 then I'd have a typo in that rule, obviously. But this? I don't get it. Anybody have an idea?

(I can't post pictures of the results from here at work. I'll take screen snaps and email them home so I can post them later.)

 

[pgc01]

e.g. 03.1E+04 instead of the correct 31.4E+03.

Hi

For ex., for numbers between 10,000 and 99,999 use the format #00.0E+0

 

[jqavins]

OK, that seems to have worked, and thank you. I added the # to the rule format and the bad cells straightened out while the good ones were unaffected.

But can you explain what's going on? Why would the lack of a # only effect half of the cells which that format applies to? Also, when I do the same to the other two rules, i.e. #000E+00 and #0.00E+00, they get hosed up, and not in the same way; #0.00E+00 messes up half of its cells the way the lack of # messed up 00.0E+00, but #000E+00 messes up all of the cells it applies to.

Fine, it's working with the # applied to one and only one of the three formats, so I guess that's good enough, but I really would like to know what the heck is going on. Until now I though I was really good at this stuff.

 

[pgc01]

Hi

There are 2 possible meanings of a 0 in the number format definition of a number: it can represent a significant digit or a padding 0. For ex., if I have an integer and use the number format 00 this means that I'll always have at least 2 digits. If it's a number less than 10 excel will add a padding zero, for ex. 2 will be displayed as 02.

The question in this thread is how this is dealt with in the case of the scientific notation.

I had this problem before and this is my conclusion on the logic of the scientific notation related to the number of digits of the integer part of the significand and the possible values for the exponent:

In format scientific notation
- the exponent is a multiple of the maximum number of digits in the integer part of the significand
- the integer part of the significand has no more digits than the ones specified in the format

Examples:

In 00.0E+0, since the significand has 2 digits in the integer part, the exponents will be 2,4,6,8,... and the significand has always 2 digits. For ex. 1234 is displayed as 12.3E+2 and 12345 as 01.2E+4

In #0.0E+0, since the significand has up to 2 digits in the integer part, the exponents will be 2,4,6,8,... and the significand will have 1 or 2 digits. For ex. 1234 is displayed as 12.3E+2 and 12345 as 1.2E+4

In #00.0E+0, since the significand has up to 3 digits in the integer part, the exponents will be 3,6,9... and the significand will have 2 or 3 digits. For ex. 1234 is displayed as 01.2E+3, 12345 as 12.3E+3 and 123456 as 123.5E+3

In ##0.0E+0, since the significand has up to 3 digits in the integer part, the exponents will be 3,6,9... and the significand will have 1, 2 or 3 digits. For ex. 1234 is displayed as 1.2E+3, 12345 as 12,3E+3 and 123456 as 123.5E+3

 

[jqavins]

Yup, that explains it all. 00.0E+0 forces an even exponent, but since half the time I wanted even exponents it worked OK for those, but not for the odd ones. 000E+0 forces a multiple of three, which is what I wanted. 0.00E+0 is equivalent to regular scientific notation, which could have any exponent, but my rules only use it when regular scientific has a multiple of three anyhow. So only the 00.0E+0 case needs help, and only half the time.

Mystery solved. I feel better. Thanks.

 

[pgc01]

I'm glad it helped. Cheers!