7. Perform Logical Tests
Many times, you need to set a cell’s value based on the results of one of more other cell values. For instance, in the worksheet for the shipping supplies company, the Order Alert column is either blank (if there’s plenty of stock on hand) or it contains the
Order Soon! warning (when inventory is getting low).
How can one cell display two possible values? By using a logical function. Each of these spreadsheet apps includes a number of logical functions; the three particularly useful ones below appear in all three apps.
The first one lets you sum numbers based on whether or not they satisfy a condition you define.
=SUMIF(TEST_RANGE,CONDITION,OPTIONAL_SUM_RANGE) (If you leave out
TEST_RANGE is also used as the range of values to sum.)
As an example, using the worksheet for the shipping supplies company, this formula will total the Qty On Hand column, adding only those items for which the Profit is more than $2.25:
In this formula, the Profit column (
D2:D8) is compared to the test (is the value greater than
2.25?); if it’s greater, then the amount in column E (
E2:E8) is added to the sum.
The second function,
SUMIFS, is related to
SUMIF, but can take many optional pairs of test ranges and conditions:
For example, if you want the total quantity on hand for items with profit over $2.25 and build cost under $3.00, it would look like this:
=SUMIFS(E2:E8,D2:D8,">2.25",B2:B8,"<3"). The formula will sum the Qty On Hand column (
E2:E8), but only if both the profit (
D2:D8) is over $2.25 (
">2.25"), and if the Build Cost (
B2:B8) is under $3.00 (
The third (and, for me, most often-used) logical function you should know how to use is the simple
IF function. Using
IF is a great way to vary cell results based on conditions being met or not being met. The syntax is pretty simple:
As one example, consider the worksheet of the shipping supplies company; the Order Alert column consists of nothing but
IF statements that all look like this one from cell G2:
The condition being tested is whether or not the ratio of the number of items on hand to the reorder point is less than
1.25 (125 percent). If it is, it’s time to order, and the
"Order Soon!" warning appears. If it’s not, then all is fine, and we leave the cell empty by specifying an empty string as the False result.
IF statements can get very complicated, as they can be nested and can include not just static text, but also references to other cells, or even ranges of cells.
8. Mix Text and Formula Results
In the section on number formatting, I explained how to add text to a custom number format (in Excel and Sheets; not in Numbers). While this works, there are other ways of mixing text and numeric results, thanks to string-based formulas.
Consider our hypothetical shipping supplies company again. Assume you want to prepare a report for management, showing the total investment (Build Cost X Quantity On Hand) for a given product. Of course you could quickly do the math and then type out an email with the details.
Instead of doing that yourself, though, you could have your spreadsheet app build the sentence for you:
“The total investment in Tape is $262.50 (we have 150 in stock at $1.75 each).”
To do this, you need a special character: the ampersand (&). The ampersand can be used to join one formula value, including text strings, to another.
So, for example, to build the sentence above (in any of the three apps), the formula would look like this:
="The total investment in "&A8&" is "&DOLLAR(B8*E8)&" (we have "&FIXED(E8,0)&" in stock at "&DOLLAR(B8,2)&" each)." The trick here is to use the
FIXED functions; these convert numbers to text that looks like numbers; the
DOLLAR version function automatically formats in currency, complete with the dollar sign.
9. Use Conditional Formatting
As covered earlier, you can format numbers, text, and cells in all three of these spreadsheet apps. But all three share a limitation when using traditional formatting: once something is formatted, it retains that format regardless of what might happen to the data in the cell. If you’re creating the line dividers in your table of data, this isn’t much of a problem. But if you’re trying to highlight a specific type of number (when you reach the inventory-order point, say), then fixed formatting isn’t much help.
That’s when conditional formatting is really handy. It’s just what it sounds like: Formatting that changes based on conditions you specify. Numbers refers to this feature as Conditional Highlighting, and you’ll find it in the Cell tab of the Format sidebar. Both Excel and Sheets refer to it as Conditional Formatting, and you’ll find it with that name in the Format menu of both apps.
Conditional formatting is a complex topic; to fully explore it requires many more words than we have here. But as one example of what it can do for you, consider the
IF function example, used earlier to fill in the Order Alert column. Instead of having the formula simply display an alert when supplies are low, we can modify the formula to display a message stating that there’s plenty of inventory. That change is simple:
=IF(E2/F2<1.25,"Order Soon!","Stock OK").
Order Soon! alert should be bold and red. But that doesn’t make sense for
Stock OK; that might be better rendered as light green and not boldfaced. By creating a conditional highlighting/formatting rule, it’s possible to change the cell’s format based on the value.
Excel: Select the range from G2 to G8 (in this example), then select the Format > Conditional Formatting menu item. In the new window that appears, click the plus sign (to add a new rule). When the New Formatting Rule window appears, set the Style pop-up to Classic, which will open yet another window (that’s three windows, and you haven’t even created a single rule yet).
In this newest window, leave the Style pop-up set to Classic, then set the second pop-up to Format Only Cells That Contain. Set the next two pop-ups to Specific Text and Containing, and then type
Order Soon! in the text box. Set the Format With pop-up to Custom Format, which will open a fourth window. Click on the Font tab, and set the Color pop-up to red, and click the Bold entry in the Font Style box. Make sure nothing is filled in on the Border and Fill tabs, then click OK (to close the fourth window), and click OK again (to close the third).
Now do the same thing again, starting with the plus-sign click. But this time, set the text box entry to
Stock OK, set the font color to green, and make sure the font style is normal, not bold. When you get back to the Manage Rules window, you should see both rules listed; click OK to (finally!) apply the rules.
Numbers: Select the range of cells (G2:G8), then click the Conditional Highlighting button on the Cell tab of the Format sidebar. This will change the sidebar; click the Add A Rule button to display a pop-up list of rules, then click the Text tab. Click the Is entry, set the first pop-up to Text Is, and type
Order Soon! in the box. Click the triangle in the menu below the text entry box, and select the final entry, Custom Style.
This will add yet another panel to the sidebar; select a red tone in the color wheel, and click on the B button for bold text. Finally, click Done.
That formats the
Order Soon! cells. But what about the
Stock OK messages, which are presently just black text? With the G2:G8 range still selected, click Show Highlighting Rules in the sidebar, then click Add a Rule again. Repeat the steps as above, except change the text box to read
Stock OK, and set the custom style to have nonbolded green text. Click Done, and you’ll see both bold red and normal green text in the cell.
Sheets: Select the G2:G8 range, then select the Format > Conditional Formatting menu item. In the dialog box that appears, set the first pop-up to Text Contains, then type
Order Soon! in the text box. Check the Text box to format the text, then click the next seemingly empty box to the right in order to display the color picker; choose a nice shade of red. You might also want to check the Background box and set a background color (bright yellow will get your attention), because Sheets doesn’t let you alter the font’s appearance with conditional formatting.
Click the Add Another Rule link, and repeat the steps, but type
Stock OK in the text box, and use green for the text color. After you’ve set up the second rule, click Save Rules to see the results of your work.