Changing Text of Formula Result Based on Return Value (133049)


The information in this article applies to:

  • Microsoft Excel for Windows 95 7.0a
  • Microsoft Excel for Windows 5.0c
  • Microsoft Excel for Windows NT 5.0
  • Microsoft Excel for the Macintosh 5.0a
This article was previously published under Q133049

SUMMARY

In Microsoft Excel, a function cannot automatically format text based on the type of value it returns. However, you can create a custom number format that will return different colored text, based on whether your function returns a positive, negative, or zero value.

MORE INFORMATION

In Microsoft Excel, number formats are based on the following: 
   [color1]positive# format;[color2]negative# format;[color3]zero# format
To have a function return up to three different types of text value, each with a different color, have the function return either a positive number, a negative number, or a zero value, and format the custom number format such that it displays text based on those values. For example, the following custom number format 
   [red]"after";[blue]"before";[green]"same"
will display the word "after" in red if the function returns a positive number, the word "before" in blue if the function returns a negative value, and the word "same" in green if the function returns 0.

Example

  1. Type the following formula into a worksheet:

    A1: =IF(A5>DATEVALUE("2/28/95"),1,IF(A5<DATEVALUE("2/28/95"),-1,0))

  2. Apply the following custom number format to the cell: 
          [red]"after";[blue]"before";[green]"same"
  3. In cell A5, type 6/1/95.

    Cell A1 will display the word "after" and the word will be formatted in red.
  4. In cell A5, type 2/28/95.

    Cell A1 will display the word "same" and the word will be formatted in green.

REFERENCES

"User's Guide," version 5.0, Chapter 12, "Formatting a Worksheet"

For more information about Custom Number Formats, choose the Search button in Help and type:

number formats, creating custom

Modification Type:MinorLast Reviewed:8/19/2003
Keywords:kbinfo KB133049
©2003 Microsoft Corporation. All rights reserved.