Abstract
Transcript
One thing you'll frequently do in Excel is check or debug formulas.
In this video, we'll look at how to use the F9 key to quickly break a formula down into pieces that you can understand.
Here we have a simple list of names. In addition to names, we have a column for birthdate, a column for age, and a column for legal status.
We've covered this before, but whenever you inherit a new worksheet, make sure you understand first where the formulas are. You can do this quickly by using Go To Special > Formulas.
You can see that both Age and Status are calculated values.
Now let's use the F9 key to see what the STATUS formula is doing.
In general, it's easier to debug a formula in the formula bar. And you may want to expand the formula bar to give yourself more space to work.
Once you're in Edit Mode, you can use the F9 key to check the calculated value of any part of the formula.
Although you can select parts of the formula manually, the Function Tooltip makes it much easier to select things precisely.
To display the tooltip, click directly on or inside any function. For example, when we click anywhere inside the IF function, we see its three arguments listed in the tooltip, and we can simply click to select each argument in the formula.
Once you have an argument selected, press the F9 key to evaluate. In this case, H5 evaluates to "Minor", H6 evaluates to "Adult", and the logical test evaluates to FALSE since Michael is only 12 years old.
Press the Escape key to exit the formula editor without making changes.
Pay attention to your selection before you press F9. If the cursor is outside the last set of parentheses, or if nothing is selected, you'll see only the final result of the formula when you use F9. Again, press Escape to exit without making changes.
Now let's look at the formula that calculates Age.
You can see that the formula uses two functions: YEARFRAC and INT.
Working from the inside out, we see the start date and end date both evaluate to date serial numbers. Next, the YEARFRAC function returns years as a fractional value. And finally, the INT function gives us just the integer value, which corresponds to a person's age as of today.
Use the F9 key whenever you want to understand or debug a formula. It's a great way to quickly understand how a formula works, and it will save you the trouble of breaking the formula down into separate formulas on the worksheet.
