The logical functions are some of the most versatile tools in Excel. They can be used to automate calculations, customize displays, and provide decision options. The example in this section introduces a basic decision statement. In this example, a company has a procedure for generating commodity prices. Figure 2-19 shows the calculation on a worksheet.
A retailer buys commodities from a supplier and gets a discount. The retailer also gets a cash discount. The delivery costs are added to the cash purchase price, and after other costs (such as rent and salaries) are considered, the primary cost amounts to $4,482.82.
The retail price for the customer has to include a discount, and the sales representative has to be paid. The difference between the cash sales price and the original costs is the profit. The profit is calculated as a percent of the original cost.
Each stage of the calculation is performed in Excel. The profit is calculated from
=D14-D12
and the percentage as
=D13/D12
But how does Excel know if the required profit is OK? Let’s assume that the retailer is content with 20 percent. He enters the following formula in cell C21 (in the figure this is shown linked with the next cell):
=IF(C13>=20%,"OK","Too low")
The IF() function checks the percentage and performs a calculation depending upon the result. The function checks whether the value in cell C13 is greater than or equal to 20 percent. If the value is greater than or equal to 20 percent, the text “OK” is displayed. If it is not, the alternative text “Too low” is displayed.
This example checks only one condition, but it can be extended to more complex conditions with additional logical functions, such as the AND() function and further IF() statements. For instance, perhaps the retailer doesn’t want to reduce his profit but needs to consider the competition as well as the price ceiling. He might want to visually emphasize the cell containing the percentage profit. In Excel 2007 and Excel 2010, he can open the dialog box shown in Figure 2-20. The New Rule option can be reached from the Home tab by clicking Conditional Formatting in the Style group.
If you are using an earlier version of Excel, select Format/Conditional Formatting.
The formula
=AND(($C$13>=20%),($D$19<6500))
verifies whether two conditions are met:
The value in cell C13 is greater than or equal to 20 percent.
The entered sales price is less than $6,500.
If both conditions are met, the cell turns green.
See Also
Chapter 9, considers logical functions in more detail.