Jon has found a clever way to show words (singular, plural or zero) with many numbers in Excel using conditional formatting.
We already shown you several ways to put singular or plural words against numbers in Excel – like 1 cookie or 3 cookies. At the end we noted how it got more complicated when you tried to deal with one, zero and negative numbers as well because Excel’s cell formatting has limitations.
Jon E. has come up with a clever solution which uses conditional formatting to split negative and positive cell values then have formatting appropriate for each side of zero.
You need TWO custom numbers, one for the positives, one for the negatives.
what I used for the positive values:
[=1]0 “day late”;[>1]0 “days late”;”On Time”
what I used for the negative values:
[=-1]0 “day early”;[<-1]0 “days early”;”On Time”
the key is to have a conditional format on all the cells telling it to use the positive custom format for cells >0 and the negative values for cells <1. (I guess you could also use >-1 and <0)
then you’ve got positive, negative, singular, plural, AND zero all at the same time! “
Those rules when setup in Excel look like this:
One rule handles cell values greater than zero and the other for zero and less. In fact zero can be formatted by either rule.
In the format dialog choose Custom and paste in the formatting code above, with words changed to suit your needs.
The result is a cell with the value alone in the cell but displayed in the form of readable text.
Very nice, great work Jon!
Singular / Plural text in Excel
COUNTIF for Excel
Excel how to remove both items if a duplicate
Microsoft fixes a big hassle with Excel’s great Lambda()
Easily compare a series of values with ChiTest() Excel
Text and formatting tricks for Excel