If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
custom number format
Scientific notation To display numbers in scientific format, use "E-,"
"E+," "e-," or "e+" exponent codes in a section. If a format contains a zero (0) or number sign(# ) to the right of an exponent code, Excel displays the number in scientific format and inserts an "E" or "e". The number of zeros or number signs to the right of a code determines the number of digits in the exponent. "E-" or "e-" places a minus sign by negative exponents. "E+" or "e+" places a minus sign by negative exponents and a plus sign by positive exponents. ---------- This is from the help file "Create or delete a custom number format" However, when I use e+/e- (instead of E+/E-) to define the format and click "ok", it said that "microsoft office excel cannot use the number format you typed". I don't know what's wrong here? Another question is that when I specify the integer part digits, say, I define a format "0.#E+#" for 3012.1, the display is 3.E+3, and if I define as "00.#E+#", the display is 30.1E+2, and if "000.#E+#", the display is 003.E3 I don't know how does excel work here... |
#2
|
|||
|
|||
It is a lot easier to provide a solution based on what you need or want
than how something works. At first it looks like you give no examples of what you entered but I see they are buried within paragraphs, Please try to separate things into paragraphs and columns (Excel people like columns) to make things more readable. See if this answers your question. The formats are essentially the same as other formats except for the exponent. 0, indicates a mandatory placement of a digit, #, indicates a place holder as far as spacing goes, leading zeros will not show. I don't think trailing zeros will be held as a space. This is your basic Scientific Notation that is generated with 2 decimal places 0.00E+00 i.e. 2.22E+8 This is a variation for Engineering Notation (exponent divisible by three) ##0.0E+0 i.e. 222.2E+6 More information in HELP, to help with number formats in help see http://www.mvps.org/dmcritchie/excel/formula.htm Actually your first question does not say what you entered, so can't help you with that. Second example is explained above, but the # immediately before the E is useless. Without the E, the # following a period would allow you to align numbers with various decimal places on the decimal point. If you want to force a space before the E then use 0.00_8E+0 If you do not want to see + after the E use E-0 which will only show negative Exponent values seems help is a bit wordy -- but it is not ambiguous. and clearly tells you that you would see E0 not E-0 or E+0 in a display. If you want to experiment, suggest you install GetFormula and GetFormat from my formula.htm page, and look at your results with various formats. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "cac" wrote in message ... Scientific notation To display numbers in scientific format, use "E-," "E+," "e-," or "e+" exponent codes in a section. If a format contains a zero (0) or number sign(# ) to the right of an exponent code, Excel displays the number in scientific format and inserts an "E" or "e". The number of zeros or number signs to the right of a code determines the number of digits in the exponent. "E-" or "e-" places a minus sign by negative exponents. "E+" or "e+" places a minus sign by negative exponents and a plus sign by positive exponents. ---------- This is from the help file "Create or delete a custom number format" However, when I use e+/e- (instead of E+/E-) to define the format and click "ok", it said that "microsoft office excel cannot use the number format you typed". I don't know what's wrong here? Another question is that when I specify the integer part digits, say, I define a format "0.#E+#" for 3012.1, the display is 3.E+3, and if I define as "00.#E+#", the display is 30.1E+2, and if "000.#E+#", the display is 003.E3 I don't know how does excel work here... |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional format number | goss | General Discussion | 1 | October 8th, 2004 03:53 PM |
Automatically set number format | Norm2000 | General Discussion | 2 | September 5th, 2004 03:57 PM |
Number Format Problem | Lana | Worksheet Functions | 6 | February 16th, 2004 08:57 PM |
Number format problem in CSV files | Mervyn Thomas | Worksheet Functions | 3 | January 14th, 2004 05:31 PM |