Microsoft KB Archive/133049

= Changing Text of Formula Result Based on Return Value =

Article ID: 133049

Article Last Modified on 8/19/2003

-

APPLIES TO


 * Microsoft Excel 95a
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0a for Macintosh

-



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
 Type the following formula into a worksheet:

A1: =IF(A5>DATEVALUE("2/28/95"),1,IF(A5  Apply the following custom number format to the cell:      [red]"after";[blue]"before";[green]"same"  In cell A5, type 6/1/95.

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

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

<div class="references_section">