Chapter 13. Advanced Formula Writing and Troubleshooting
Over the last five chapters, youâve learned how to use Excelâs impressive function library to calculate everything from statistical trend lines to payment schedules for home mortgages. Now that youâve had a close look at these functions, itâs time to consider a few techniques to help you get the most from your formulas.
In this chapter, youâll tackle four new topics thatâll make you a formula master:
How to use conditional logic with functions like SUMIF() and COUNTIF().
How to make formulas more readable by using named ranges.
How to control when Excel recalculates your worksheets.
How to solve mysterious errors by using Excelâs formula auditing tools.
Conditions in Formulas
Chapter 8 gave you a first look at using conditional logic in Excel formulas. The basic principles are easy: You construct a condition using the logical operators <, >, =, and <>, and then use that condition with a conditional function. So far, youâve focused on one conditional functionâIF()âwhich performs different actions depending on the result of a calculation.
Hereâs an example that uses the IF() function. The following formula carries out the operation in either the second or third argument, depending on the value of cell A20:
=IF(A20>10000, A20*5%, A20*3%)
Translation: For values greater than 10,000, Excel executes the formula A20*5%; otherwise, it carries out the second formula. If A20 contains the dollar amount of a sales invoice, ...
Get Excel 2013: The Missing Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.