Excel: Using Conditions in Number Formats

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 "condition" clauses. Only they can contain conditions. If conditions are omitted, they default to "greater than zero" and "less than zero."

The third clause is the "else" 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 "zero" case, as in Excel 2.20 and earlier.

The fourth clause is always "text," 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 "[>3][Red]0.00;[<6][Blue]0.00;0.00" is displayed Red. Also, with the format "[Red]0.00;[>5][Blue]0.00", positive numbers (even those greater than 5) are never displayed in blue because the default condition of the first clause "[>0]" takes priority over the "[>5]".

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 "Invalid number format" alert is given.

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

KBCategory: kbother
KBSubcategory:

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.