There is one part of spreadsheets that feels especially like computer programming: the IF function. IF function makes spreadsheets incredibly powerful and when used correctly, you can produce almost any output you want with them.
How IF functions work
The way IF functions in Excel, Google Sheets, Subset, and most other spreadsheets work is "if some logical test is true, then give me x value, otherwise give me y value (if that logical test is false)."
A simple use case for this would be to check if a number is negative and if so, return a string of text. The good thing about IF function is that once you create one, you can re-use them and drag them across your spreadsheet like any other formula. In the example below, we will try to return "Positive" if the number is greater than 0 and "Negative" if the number is less than 0.
IF Function: =if(a1 >0, "Positive", "Negative")
Notice that I only put in one logical test: if the number is greater than 0. As a result, that’s the only thing the formula will check. So if I put in a negative number, the output will be “Negative” since the logical test failed. However, the test would also return "Negative" if I put in the number 0, even though it's not a negative number. Why? Because Excel still will only test it through the logical test, and 0 is not greater than 0. So in that case, the logical test still fails, and it will return the second string, which in this case is "Negative."
A simple way to think about it is if the logical test returns with TRUE, then you will get the first value, otherwise (if logical test is FALSE) you will get the second. Like computer programming, Excel takes the logical test extremely literally and cannot make any human inferences.
There are a couple of ways to fix the formula to get it to work with 0. You can make the logical test include 0, which you can decide if 0 will go into the Positive or Negative bucket. Or if you want to be more precise, you can create **another** IF formula into the False section of the formula for the text “Zero” to return. So this way, if the first test fails, it will go and run another formula. An updated formula would look something like this: `=IF(a1>0,"Positive",if(a1<0,"Negative","Zero")).`
Some other functions you can use in the logical test are the following:
```
= for equality
> for greater than
>= for greater than or equal to
< for less than
<= for less than or equal to
<> for does not equal
```
And now you can use your imagination and see that you can nest as many IF functions in here as you'd like and make the formula infinitely powerful. Keep in mind though that as you nest more IF functions in a single formula, it becomes harder to a new person to audit or understand if you are using your spreadsheet collaboratively. I think its best practice to try and make formulas as simple as possible, especially if I am eventually sharing them, so oftentimes I'll break out one large IF formula into multiple mini IF functions.
Common formulas nested within IF formulas
What if you wanted to run multiple logical tests? Luckily you can do that with the AND and OR functions. All the logical test has to be able to do is return a TRUE (to give the first input) or a FALSE (to give the second input). Since the AND and OR function returns a True so long as all criteria for AND and one of the criteria for OR works, you can nest these within the IF function.
A sample formula would look like this:
```
=IF(AND(a1>0, b1>5,c1>100),"Yes", "No")
=IF(OR(a1>0, b1>5,c1>100),"Yes", "No")
```
Variation of the IF Formula - the IFERROR
Our formulas have been fairly simple, but as you can imagine, the formulas can get pretty complex. Sometimes when the logical tests get so complex, it's easy to not account for something. In these cases, Excel will spit back an Error that could look something like #NA. One trick to remove these is to use the IFERROR function. What this says is if there is an error, do something else. A simple trick to clean up the spreadsheet is to do a formula like this:
`=IFERROR(SUMIFS(range, range, "="&a1),"")`
The way this formula works is if the first part of the formula returns back with an error, instead of the error, return the second part of the formula (which you can make another formula or a string of text (in the case above, I returned a string of text " " to represent a blank, but you can put in anything here).
Conclusion
There are two more IF functions that you use all of the time in financial analysis and that is SUMIFS and COUNTIFS, which we will cover in the next lesson. In the meantime, practice the IF function today since it's one of the most powerful ways to use spreadsheets.
