In this brief video, we look at how to debug a formula using the F9 key. If you work with formulas a lot, this is one of the most useful skills you can learn.
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 birthday, 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 can do this quickly by using goto 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 room 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 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 an argument is 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, 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 need 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.
The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions....
The Excel YEARFRAC function returns a decimal value that represents fractional years between two dates. You can use YEARFRAC for formulas that do things like like calculating age in years using a birthdate.
The Excel INT function returns the integer part of a decimal number by rounding down to the integer. Note the INT function rounds down, so negative numbers become more negative. For example, while INT(10.8) returns 10, INT(-10.8) returns -11.
I LOVE your site. It helps me frequently as I have no formal training but I'm very logic oriented and this site is put together so very well and has assisted me so very much! You've improved the quality of my life. -Mike