Excel: Custom scientific notation format not working


I would like to modify the standard scientific notation format so that it shows 2 decimal places to the left and none to the right. So 123 million (123,000,000) would be "12 E+07" rather than "1.2 E+08". Here's an example. The default format is in C. The format I tried is in E. It works perfectly in E4 & E5, but in E6:E9, it has a leading zero. How can I get those to work like the ones above?

C/R C D E F
3 0.0 E+00 Formula 00 E+00 Description
4 6.4 E+11 =COMBIN(52,13) 64 E+10 Number of possible hands (13 cards)
5 8.1 E+67 =FACT(52) 81 E+66 Number of possible deals (4 hands)
6 20,136 =NumberHands/60/60/24/365 02 E+04 Years at one hand/second
7 2.6 E+60 =NumberDeals/60/60/24/365 03 E+60 Years at one deal/second
8 7.5 E+18 7.5E+18 08 E+18 Number of grains of sand on earth
9 1.4 E+10 13800000000 01 E+10 Number of seconds since the Big Bang
Thanks


This question generated 11 answers. To proceed to the answers, click here.

This thread is current as of February 25, 2017.


For more resources for Microsoft Excel