1. Working with conditions and case statements
In this chapter, we'll be moving away from the schedules that we built in the last chapter, and towards dashboards which will be able to take different types of user input.
2. Schedules vs. Dashboards
So far in this course, we have been working on schedules. These have been sheets for one scenario at a time.
For example, we knew that when we had a monthly amortization, we needed to divide rates by 12 and multiply years by 12. But what if the amortization was bi-weekly instead?
We would need to be able to tell users to change all the 12's to 26es... and it might require a long phone call!
Instead of dividing by 12, why not make a formula which changes on user input?
3. The IF() formula
The first conditional formula we'll look at is the IF() formula.
This formula looks at a single condition, and if the condition is met, then it provides the true value, otherwise the false value.
In this case, if it is a monthly frequency, it will provide 12, otherwise it will return 1 for annual.
4. Nested IF() formula
But what happens when there are more than 2 conditions?
One way to solve the problem is with a nested IF(); that is to put more IF() formulas where the else clause is.
It is very easy to lose track of all the brackets that you need to make this work! Also, how is a user to know that the 1 at the end means to default to annual payments?
Fortunately, there are formulas to make your life easier in these cases!
5. Time to SWITCH() things up!
One way of being able to use multiple conditions on a spreadsheet is with a SWITCH() function. Using the switch function, you can take one value in a cell, and convert it to another value based on the value in the cell.
So, for our example of amortization frequency, we can convert weekly to 52, bi-weekly to 26, and so forth.
There are some limitations to SWITCH(); like there is still no clear "else" for a default value, but it is much easier to keep track of than a nested IF!
For those who are using Microsoft Excel, this function is only available in version 2016 or later.
6. IF() you don't succeed, try IFS()!
The SWITCH() statement requires a specific value; one way around that is with an IFS() formula.
In an IFS() formula, instead of returning values based on other values, it returns based on conditions.
For example; if the date is greater than 359 days, it will return daily. This is important, since some schedules use a 360 day year, while others use a 365 day year.
The limitation of IFS() is that there is no default value. If a user were to type in 3 for quarterly, it would return an error.
A way around this error is to enter a condition which is always true at the end of the formula; like 1 = 1. This will act like an else statement.
7. Time to practice with conditions!
Now that we've gone over the IF(), IFS() and SWITCH() formulas, it will be a good time to get some practice with them.
The practice exercises that you'll be working with aren't related to loan amortization - they are just good cases for practicing these formulas.
We'll be using them a lot for the rest of the course, so it's important that you really master these formulas before moving on.