By Jen Sweeney
A big chunk of our support calls come from corporate workers looking for help with Excel formulas and functions. Creating formulas can be intimidating, and, if they are not constructed properly, will return a workbook full of errors and agita.
With each new version of Excel, Microsoft makes formula-building easier for sure. But no amount of automation can replace a solid understanding of the basics of formulas and functions. In particular, if you get Excel’s use of logic, you will always be one step ahead. (But, of course, you can continue to call us for help. We enjoy creating formulas.)
Microsoft Office 2013: Understanding Excel’s Use of Logic
We use logic every day to help us make decisions — “If I go to the store, and they have eggs, I will buy some. If they do not have eggs, I will not buy them.” Excel is no different, only we use logic in Excel to examine and work with data.
When we use a formula in Excel to compare two values or to perform calculations with criteria, Excel uses logic to determine the results. A simple example would be comparing two numbers. If they are equal, then Excel will return TRUE. If they are not equal, Excel will return FALSE.
When using a logical function such as COUNTIF (to count values in a range that meet a specific criteria), Excel looks at each cell in the range and then determines whether or not that cell meets the criteria. It then converts that range from cell references to a list of TRUE and FALSE results and then counts the TRUE values to determine how many cells meet the criteria for the COUNTIF function.
Excel is able to add TRUE and FALSE values because Excel assigns numerical values to TRUE and FALSE. TRUE has the value 1 and FALSE has the value 0. So the list of TRUE and FALSE values becomes a list of 1’s and 0’s. You can actually test this in a cell by typing the formula =1+TRUE. The result will be 2.
Knowing how Excel works with TRUE and FALSE results in formulas can help you build powerful logical calculations. Additionally, knowing what Excel does with the data enables you to better troubleshoot errors in logical formulas.
Fine-tune your formula-building skills using these related tips: