How does a custom formatting works for E-notation?

Question

Hi!

Does anyone can explain why adding "#" digits placeholder for E-notations work in the next way?

For example, I have an number 1.23456789E+25 and try to format it with custom number format as "#####.00E+00".

And the result is  "1.23E+25", but I'd expect to see "12345.67E+22". Because I have five digits placeholder "#" in the integer part.

Following this:

https://support.office.com/en-us/article/Create-or-delete-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4

Best regards,

Valentin

Answer 1

So, for example, with the format #####.##E+0, 1234567890 is displayed as 12345.68E+5.

Yes, but try for this case format ####.##E+0

 and you'll get 12.35E+8 instead of 1234.5678E+6.

That's not logical.

Answer 2

I made a mistake: I meant to the left of the decimal point (at integer part of the mantissa)

when I want to see more digits to the right of the decimal point

Answer 3

Hi, Kevin!

You've talked about normalized form of scientific notation

https://en.wikipedia.org/wiki/Normalized\_number

But, considering custom formatting I can format number in denormalized scientific notation, when I want to see more digits to the right of the decimal point: for example for number 1.23456789E+10, I want to see five digits at the integer part and thus I use the next custom formatting pattern "#####.00E+00" and expect to see "12345.6789E+06", but excel shows 1.23E+10.

Is that correct behavior or a bug?

Answer 4

zorvek wrote:

Scientific notation always displays only one digit to the left of the decimal point.

That is true of the format called Scientific (uppercase) only because it is the format 0.00000E+00.

But we can have customized "scientific" (lowercase) formats -- "E-notations", as Valentin calls them -- with a varying number of digits to the left of the decimal point.

Here are some examples from Excel 2007:

I believe the format with 2 zeros or #'s to the left of the decimal point ensures that the power of 10 after "E+" is an even number (multiple of 2).

I believe the format with 3 zeros of #'s to the left of the decimal point ensures that the power of 10 is a multiple of 3, sometimes called "engineering format".

In general, I believe that if there are n zeros or #'s to the left of the decimal place, the value is displayed with a power of 10 that is a multiple of n.

So, for example, with the format #####.##E+0, 1234567890 is displayed as 12345.68E+5.

I cannot say that with "authority".  That's just what I infer from my own experiments.

Answer 5

Significant digits are not handled that way when using scientific notation. They are always displayed to the right of the decimal point. Scientific notation always displays only one digit to the left of the decimal point.

Use this format:

0.00000E+00

Kevin