In Excel there are various options for making sure a text label singular (e.g. person) or plural (people) matches the quantity.
For example if the cell has 1 item you want the singular label:
1 cookie
But if the value is more than one, it’s nice if the label changes:
2 cookies
IF statement
Use this method when the text label is in a different cell to the value (in A2).
=IF(A2=1,”Cookie”,”Cookies”)
However this simple example doesn’t allow for zero cookies (horror!) . To handle that you need to nest IF statements:
=IF(A2=1,”Cookie”,IF(A2=0,”Nil, Nada”,”Cookies”))
First test for the value 1, if that doesn’t apply test for 0 and if both don’t apply then it’s 2 or more.
To change the formatting of the cells according to value you need to use Conditional Formatting.
Update: Reader Bob J. suggests another path to a similar result:
I wonder if it is not grammatically correct to say “0 cookies” which makes the problem easier.
So to format a cell to put the cookie count in cell A2 in ‘english’ in another cell you could write.
=text(A2,"0")&" Cookie"&IF(A2<>1,"s","")
which produces:
0 cookies
1 cookie
2 cookies
etc.
The Text() function converts the number into a text string so it can be joined to other strings.
and to add the custom formatting consistent with the above use:
[=1]0 "Cookie";[<>1]0 "Cookies"
Cell Format
A more powerful option is cell formatting either directly or as a cell style. The latter is recommended because it’s easier to manage and apply to multiple cells.
Cell formats tell Excel how to display the underlying cell value. There are many supplied formats for numbers, currency, date, time etc but there’s also the Custom option.
Custom is a powerful and somewhat confusing set of text strings that let you change the cell’s appearance according to value. Sadly, as you dig into this feature you’ll find some annoying limitations imposed by Microsoft, details below.
We’re only going to touch on a few of the features, but first here’s a simple example.
$#,##0;-$#,##0
This put a dollar sign in front of the value plus a comma separator if necessary. The # symbol indicates digit places to be used if necessary. After the semi-colon is the format for negative values – in this case it’s the same with a minus sign before the dollar sign.
You can add text into the cell that contains the value, rather than an adjoining cell that we used in the IF method above.
You can also have conditions in the formatting string. This feature isn’t obvious because it’s not shown in any of the Custom string examples supplied with Excel.
Here’s a simple example:
[=1]0 “Cookie”;[>1]#,##0 “Cookies”;
As you can see, cell A11 has a number value of 3 only but displays that value and a text label in the same cell.
The square brackets in the custom format contain the condition, each set of a condition and format is separated by a semi-colon.
The above example doesn’t allow for a zero value – if the cell equals 0, the cell is blank because there’s no matching formatting.
That’s why you should add a fallback format at the end after the last semi-colon.
[=1]0 “Cookie”;[>1]#,##0 “Cookies”;General
You might think this would be another improvement:
[=0]0 “NO Cookies!”;[=1]0 “Cookie”;[>1]#,##0 “Cookies”;General
Alas, Excel is limited to only two conditions for string format so the last formatting string won’t work.
A workaround
Office Watch Reader, Carolyn, came up with a partial workaround:
[=1]0 “Cookie”;[>1]#,##0 “Cookies”;”No Cookies”
“If the cell = 1 you get “1 cookie” if the cell is greater than 1 you to “x cookies” and if the cell is zero you get “No Cookies” ”
This gets around the two conditions limit by providing a third, assumed, option that deals with both zero values and negative values.
That’s OK if you can be sure there will be no negative cell values. If there is a negative number, it will misleadingly show “No cookies” and not the correct negative value.
Assumed Conditions
Happily there is a partial workaround for the two condition limit. The standard formatting string has some assumed conditions. We’ve already seen this in practice with this format $#,##0;-$#,##0 – where the semi-colon separates the positive and negative value formats without explicit [>0] and [<0] conditions.
In brief, the order of assumed conditions is:
Positive;Negative;Zero;Text
To make that work partly for cookies:
#,##0 “Cookies”;-#,##0 “Shortfall”;”NO Cookies!”;General
But that leaves us back with no singular text label for one cookie!
Microsoft giveth and Microsoft taketh away. The limit of two conditions badly needs lifting to at least 4 conditions (to accommodate Positive, Negative, Zero and Singular (1)) if not more.
Clever solution for Singular/Plural 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
