Before we get started with advanced IF functions, let's do a quick IF function refresher first. Watch this video for some examples.
Want more?
We'll cover complex examples and variations of the IF function in a few minutes.
But first, a quick IF function refresher.
I am determining if my travel expenses are over or within budget.
If the Actual expense is greater than the Budgeted expense, Status is Over Budget.
Otherwise, it is Within Budget.
In the formula, text (like Over Budget and Within Budget) must be in quotes.
And now, I am copying the formula.
Now, I am determining if a category of merchandise requires a shipping surcharge.
If Packing is equal to Fragile (comparing text with the IF function is not case sensitive), the Surcharge is $75.
Otherwise, it is 0.
I am copying the formula again.
If the text has extra spaces, comparing text with a function may not return the results we expect.
The text in B3 has a leading space, and it is not returning the results I expect.
It should have a $75 surcharge because it is fragile.
Getting this wrong would take 75 dollars per mistake out of our revenue.
Adding the TRIM function to the formula will help.
The TRIM function removes spaces from a text string except for single spaces between words.
And now, the formula handles extra spaces in text strings.
This is the syntax, or grammar, of the IF function.
Logical_test is required.
It can be any expression that can be evaluated to TRUE or FALSE, like comparing one number or cell to another, such as C2>B2.
It can also be text, such as B2="fragile".
Value_if_true is optional. This is the value if the logical_test evaluates to TRUE.
For example, if C2>B2 is TRUE, return Over Budget.
If the logical_test evaluates to TRUE and we don't provide the value for value_if_true, the function returns 0.
Value_if_false is also optional.
This is the value if the logical_test evaluates to FALSE.
For example, if C2>B2 is FALSE, return Within Budget.
If the logical_test evaluates to FALSE and we don't provide the value for value_if_false, the function returns 0.
Up next, nested IF functions.
No comments:
Post a Comment