Microsoft KB Archive/67469

{| = Excel: Using Conditions in Number Formats =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q67469

SUMMARY
Microsoft Excel version 3.00 allows four number format clauses as does Excel versions 2.20 and earlier. These clauses must be enclosed in brackets ([]).

In Excel 2.20 and earlier, the formats have the underlying conditions; greater than zero, less than zero, zero, and text.

In Excel 3.0, the first two clauses are the &quot;condition&quot; clauses. Only they can contain conditions. If conditions are omitted, they default to &quot;greater than zero&quot; and &quot;less than zero.&quot;

The third clause is the &quot;else&quot; clause. Its format is applied to any numbers that don't satisfy the conditions (or default conditions) in the previous two clauses. If no conditions are used in the first two clauses, this covers the &quot;zero&quot; case, as in Excel 2.20 and earlier.

The fourth clause is always &quot;text,&quot; as in Excel 2.20 and earlier.

MORE INFORMATION
The following are examples:

Number  Format                                  Displayed --  --                                  -

999     [>1000][red]0.00E+00;General;0.00       999.00 1001    [>1000][red]0.00E+00;General;0.00       1.00E+03   (red) -5      [>100]General;[<100](General),General   -(5) -5      [>100]General;[<0](General),General     (5) The first clause condition takes priority over the second clause condition if there is any overlap. For example, the number 5 with the format &quot;[>3][Red]0.00;[<6][Blue]0.00;0.00&quot; is displayed Red. Also, with the format &quot;[Red]0.00;[>5][Blue]0.00&quot;, positive numbers (even those greater than 5) are never displayed in blue because the default condition of the first clause &quot;[>0]&quot; takes priority over the &quot;[>5]&quot;. The number format cannot have conditional arguments in the third and fourth clauses, and only proper conditions can be used in the first two clauses. Otherwise, an &quot;Invalid number format&quot; alert is given.

For more information on number formats, see pages 183 to 285 of the &quot;Microsoft Excel User's Guide&quot; for version 3.0.
 * }