Conditional functions and logic
1. Conditional functions and logic
Welcome back!2. Analyzing data
So far, we've explored our data to better understand it and identify potential data quality issues. We then cleaned it to ensure it can be processed more easily and return valid results. Now on to the actual analysis!3. Conditional functions
To really dig into our data to answer complex questions, we'll need to be able to use conditional functions and logic. Conditional functions return different values depending on whether a set of logical criteria are met. As human beings, we perform logic as part of the everyday decision-making process. If we're taking our dog for a walk, we may consider several criteria before deciding what to wear: if the sky is gray and the weather forecast is poor, we may decide to wear a coat or take an umbrella. We've just done a conditional calculation, where we evaluated the logical criteria of whether the sky is gray and the forecast is poor, and if they're both true, we wear a coat.4. The IF() function
The IF function can be used to return values based on logical expressions, in much the same way as we decided to wear a coat. The function has a few arguments: the logical expression to evaluate, which must return TRUE or FALSE, the value to return if the expression evaluates TRUE, and the value to return if FALSE. Let's analyze pledged funds for kickstarter games versus their goals. We'll return "Successful" if the pledged amount is greater-than-or-equal-to the goal, and "Unsuccessful" otherwise.5. The IF() function
We start our formula with the IF function.6. The IF() function
We create a logical expression by comparing the pledged cell with the goal cell, using the greater-than-or-equal-to comparison operator.7. The IF() function
We want to return "Successful" if this expression evaluates to TRUE,8. The IF() function
and "Unsuccessful" if it's FALSE.9. The IF() function
This returns "Successful" for the first kickstarter game,10. The IF() function
and for many others in the spreadsheet. This insight is really useful, but remember our example of determining whether to wear a coat on a walk? That required two criteria: the sky to be gray and the forecast to be poor. Let's now look at creating more complex logical expressions for our IF function by combining criteria.11. Logical functions - AND()
The AND function takes any number of logical expressions, and if they all return TRUE, the function returns TRUE. In this example comparing numbers, AND returns TRUE, as both criteria evaluate to TRUE.12. Logical functions - OR()
The OR function has the same syntax, only this time, the function returns TRUE if at least one criteria evaluates to TRUE. This example returns TRUE, because the first condition returns TRUE and the second returns FALSE. Remember, OR only requires one true criterion to return TRUE.13. Combining IF() and AND()
Now we can combine criteria to create more complex logical conditions with AND and OR, let's look at an example of using AND inside IF. Let's return to the kickstarter games, but this time, define successful games as ones that not only reached their goal, but also raised over 10,000 dollars.14. Combining IF() and AND()
We start with our IF function, as before;15. Combining IF() and AND()
then begin our logical expression with AND, as we are only interested in games that meet both criteria.16. Combining IF() and AND()
We add two expressions: one to compare to see if the pledged amount is greater-than-or-equal-to the goal,17. Combining IF() and AND()
and another to check if the pledged amount is over 10,000.18. Combining IF() and AND()
We complete our IF statement to return "Successful" if AND returns TRUE and "Unsuccessful" if it returns FALSE.19. Combining IF() and AND()
The first game was successful again,20. Combining IF() and AND()
but significantly less met both criteria.21. Let's practice!
Time to get logical with the following exercises!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.