Formatting engineering notation in excel 2

[Agent666]

Does anyone know any other solution to the following without going down the different formats for positive, negative and zero numbers in Excel?

For engineering notation formatting, you can put a custom cell format of "##0.00E+00", the double hash is a placeholder so only 10^0, 10^3, 10^6 etc are displayed vs scientific notation which would be 10^0, 10^1, 10^2, 10^3, etc without the hashes.

However this engineering notation format gives a true zero number of 000.00E+00 which surprised me (note the leading zeros), instead of simply 0.00E+00. Any other number won't show any leading zeros with the hashes in the format, for example 10 shows as 10E+00 as expected and not 010E+00. Those leading zeros are no big deal, but its messing with my OCD.

Is anyone aware of how to get a simple custom format of "##0.00E+00" to display a zero value correctly?

I've had to go down the "##0.00E+00;-##0.00E+00;0.00E+00" format route, but this gets a little messy as I'm using VBA to set the cell formats (a routine that toggles the formatting between 'normal' numbers and engineering notation for certain ranges). I've already solved this issue in my code constructing this string and applying it as a number format. So really just asking if I'm missing any other way to show a true zero value in engineering notation without the leading zeros?

I'm using Excel 2016 (the Office 365 version) if it matters.

 

[SkipVought]

Hi,

How about

#.00E+00

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Agent666]

Hi Skip

That's simply scientific notation, i.e. 10^0, 10^1, 10^2. You need the two extra '#'s to force the E+00 to go up in multiples of 3, aka Engineering notation.

Interesting if it was working for you, perhaps it displays differently on other versions of excel?

 

[SkipVought]

No help

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[JoelTXCive]

I came across this webpage on excel number formats a while ago and it solved many of my excel number formatting questions/issues.

Check it out.......maybe you can resolve your question. They have lots of examples. At the bottom of the page is the discussion on scientific notation......

https://www.exceltactics.com/definitive-guide-custom-number-formats-excel/

Link

 

[Agent666]

Thanks for the link. While it doesn't directly answer my query as the info deals with scientific notation which works as expected in my image, there are quite a few custom formatting tricks I didn't know about!

 

[jhardy1]

Try "Conditional Formatting" - if the cell value equals zero (or lies between a range of values that you consider to be "equal" to zero at your required precision), change the numeric format to "General" (or whatever you want zero to appear as) - this would yield the following formats:

123.46E-09
12.35E-09
1.23E-09
123.46E-12
0

instead of

123.46E-09
12.35E-09
1.23E-09
123.46E-12
000.00E+00

http://julianh72.blogspot.com

 

[Agent666]

Thanks for that, another way to do it that I had not considered.

More efficient to have the custom format though I bet. Easier to remove/customise a custom format than a conditional format, conditional format remains even if you want to change the underlying format to something else, which could get annoying (like wanting more decimal places displayed for example).

 

[Settingsun]

Somewhat related to this topic: My preferred format in a number of cases is engineering format (E3, E6, E9 etc) but with a constant number of significant digits displayed. Eg for three significant digits, 7.96324E8 would display as 796E6; 7.96324E7 would be 79.6E6. This requires the number of places after the decimal point to change depending on the number's magnitude. Is there a way to force this?

(I could live with 000E0 if that's the price of engg notation + 3 sig-figs)

 

[Agent666]

steveh49, you can do this via a series of 3 conditional formats. See attached file for an example. I've coloured the three conditions so you can tell when each is triggered for a given number scenario.

It's important to leave the bold part of the conditional formula without the absolute references (i.e. the $ signs), otherwise it won't work.
FIND("E",TEXT(B1,"###.###E+00"),1)=......

How this works is it checks where the "E" is if the number is formatted in engineering notation, and formats the three possibilities based on this for three significant figures, where there are no decimal places the "." is not included. Note the three formulas are different.
i.e. formats as ##.#E+00, #.##E+00 or ###E+00 as required.

I'd say its a bit fiddly as any cut/paste of the cells also carries over the conditional formatting which can get a bit messy. Conditional formatting also can be quite slow on large data sets. I'm not aware of any way to do it using normal formatting.

Note also that I believe earlier versions of excel were possibly limited to 3 conditional format rules per cell, so doing this would obviously use up all the conditional formats. Unsure if latest versions have extended or removed this limit.

Another way to do it would be to setup a worksheet change event on the sheet in VBA and check specific cells, and reapply a custom 'numberformat' using VBA based on the number in that cell. Any change on the sheet would trigger the code to run again and reapply the correct formatting. You could automate it to some degree by being disciplined enough to have a style you applied to cells where you wanted the format to be 3 significant figures and then just loop through all the cells with this style applied on each worksheet (or specific cells) having their inputs changed.

 

[Settingsun]

Thanks very much, Agent666.

 

[Agent666]

No problem, note you could address the true zero state without leading zeros in the custom format as well using the custom format in my first post (just change the format to include the same positive/negative format and the zero format condition without the leading zeros).

 

[SteveMills]

It is easy with the Custom Number Format per instructions at

https://excelribbon.tips.net/T012874_Engineering_Calculations.html

Basically, type in a format of
##0.00E+#0