Changing Text of Formula Result Based on Return Value

Zavrnitev odgovornosti za umaknjeno vsebino KB

Ta članek govori o izdelkih, za katere Microsoft ne ponuja več podpore. Zato je ta članek na voljo »tak, kot je«, in ne bo več posodobljen.

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
Lastnosti

ID članka: 133049 – Zadnji pregled: 19. avg. 2003 – Revizija: 1

Povratne informacije