Custom scientific notation format not working

I found this: https://excelribbon.tips.net/T012874_Engineering_Calculations.html.

Search for "Here's the interesting part".

Not a format, but shows the desired results:

=TEXT(D4,REPT("#",ABS(INT(LOG(D4))-1))&"E+00")

Ruddles said:

... it seems the exponent is always an even number, whatever value is in the cell...

Click to expand...

Not always. For each of the examples, there is a format that works. The problem is that it may not work for the others.

Tetra201 said:

Not always. For each of the examples, there is a format that works. The problem is that it may not work for the others.

Click to expand...

I realised this was wrong and edited my reply as quickly as I could. The Excelribbon article is interesting.

Ruddles said:

... I realised this was wrong...

Click to expand...

To my knowledge, the scientific format with no fractional decimals shows the exponent as follows:

##E+00 the exponent will be a multiple of 2
###E+00 the exponent will be a multiple of 3
####E+00 the exponent will be a multiple of 4
and so on.

It's odd that MS decided to tie the number of significant digitsin the mantissa to the way the exponent is displayed. I can't see the rationality behind it.

Ruddles said:

It's odd that MS decided to tie the number of significant digitsin the mantissa to the way the exponent is displayed. I can't see the rationality behind it.

Click to expand...

It is actually standard scientific notation. The E is short for "multiplied by ten to the power of", so when you write 12.34E5 that should be read as "twelve point three four times ten to the power of five", or, in number format...

12.34*10^5

which is 12.34*10000 and that becomes 1234000 when multiplied all the way out. Look at it this way...

12345 is the same as 12345*10^0 (any number raised to the zero power is 1 and 1 times any number is just the number itself), so the following all represent the same number, 12345...

etc.
12345000*10^-3
1234500*10^-2
123450*10^-1
12345*10^0
1234.5*10^1
123.45*10^2
12.345*10^3
1.2345*10^4
0.12345*10^5
0.012345*10^6
etc.

It really does not matter how you write... the OP opted for showing a maximum of two leading digits (and suppressing the decimal part if any)... the power of 10 must adjust for the given number in order that the underlying value does not become a different number from the original.

But why would they decide that if your mantissa is formatted ##, the exponent will always be a multiple of 2; if the mantissa is formatted ### then the exponent will be a multiple of 3; if the mantissa is formatted #### then the exponent will be a multiple of 4, etc? Why would the exponent need to be a multiple of anything except 1?

Why does a format of #0E+00 display 3000 as 30E+02 and not 3E+03?

How does Jennifer achieve what she wants using Excel's formatting facilities?

Ruddles said:

... Why does a format of #0E+00 display 3000 as 30E+02 and not 3E+03?..

Click to expand...

3000 formatted as 0E+00 ==> 3E+03 (exponent should be a multiple of 1; mantissa should have only 1 digit)
3000 formatted as #0E+00 ==> 30E+02 (exponent should be a multiple of 2; mantissa can have up to 2 digits)
3000 formatted as ##0E+00 ==> 3E+03 (exponent should be a multiple of 3; mantissa can have up to 3 digits)
3000 formatted as ###0E+00 ==> 3000E+00 (exponent should be a multiple of 4; mantissa can have up to 4 digits)