Clever solution for Singular/Plural in Excel

Jon has found a clever way to show words with many numbers in Excel

Back in July 2012 we showed you how 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:

Excel Conditonal Formatting rules manager image from Clever solution for Singular/Plural in Excel at Office-Watch.com

One rule handles cell values greater than zero and the other for zero and less. In fact zero can be formatted by either rule.

Excel - conditional formatting edit rule image from Clever solution for Singular/Plural in Excel at Office-Watch.com

In the format dialog choose Custom and paste in the formatting code above, with words changed to suit your needs.

Excel conditional formatting custom formatting image from Clever solution for Singular/Plural in Excel at Office-Watch.com

The result is a cell with the value alone in the cell but displayed in the form of readable text.

Excel conditional formatting - results image from Clever solution for Singular/Plural in Excel at Office-Watch.com

Very nice, great work Jon!