Learning Objectives Covered
• LO 03.01 - Demonstrate how to use IF functions
Career Relevancy
Complex functions are important for all spreadsheet users. As you become more comfortable with spreadsheets, you will realize the benefits that IF functions can provide to you and how you search and find information within your spreadsheet data sets. Learning IF functions is an excellent skill to possess and one that you can showcase in your job. Learning the advanced skills of spreadsheets will make you a valuable resource in your organization.
Background
Excel spreadsheets have the ability to conduct complex functions that allow the user to sort through data and to find specific data based on described data. One of these functions is the IF function. The IF function is considered to be one of the most utilized functions in Excel. The function has the ability to allow you to make logical comparisons between values within the spreadsheet and what you would expect. For all IF functions, there are two possible results. The results can either be True if the comparison is true or False if the comparison is false. Let’s consider an example of an IF function statement.
IF statements are not just common in programming, they are an essential part of our regular life. For example, at lunchtime at home, we open the fridge and scan what's in there. Eventually, the internal dialogue goes like this:
Is this yummy? If yes, ask the next question. If no, look for something else and start over
Yes.
Does someone else in the family already have dibs on this? If yes, look for something else and start over. If no, pull it out and eat it.
No. Eat it!
We may not think of that situation in those exact words, but it is the process we go through. Excel can do the same thing. We do have to be careful how we set things up though. For example, an IF statement might consider whether the picture is a rubber duck. The answer though depends. It is not a real rubber duck, it is a photo of a rubber duck. As you set up your IF statements, be careful about making the comparison you really want.
This IF statement above determines whether the word "yes" is listed in cell C2. If the word "yes" is placed in cell C2, the function would return a 1; if not, a two would be returned. These IF functions can be used throughout the spreadsheet in order to return information that is useful for data analysis.
One way to think about an IF statement would be considering rain and windows. An IF statement would state, "If it is raining, then close the windows; otherwise, leave the windows open." The IF statement is providing a solution (the closed window) to a specific problem (rain) that may arise. The spreadsheet function is going through and doing this exact same thought process given the inputs that the user has specified.
IF statements allow the users to quickly find the data that is needed for them to conduct their operations. Let's consider another example. You have been asked to go through the list of customers and determine who is behind on their invoices. Well, if you did not use a function, you would have to go through each cell or customer line and compare the date of the invoice with today's date. That could be extremely time-consuming depending on how many customers you have to look for. With an IF statement, we can write a command for the spreadsheet and have the values brought back to us. For this example, the customer's payment due date is posted in Column A of the spreadsheet, the payment status is shown in Column B, and the customer's name is listed in Column C. It is your role to enter the date that each customer pays the invoice. In order to make your job easier, you have decided that an IF function would be beneficial. As we noted, Column B contains the payment status, so we will place the IF statement in Column B. We want to calculate which customers are more than 30 days past due so we can send late notices. Our IF statement would be the following:
In easier terms, this formula means: If the date in cell A4 minus today’s date is greater than 30 days, then enter the words ‘Past Due’ in cell B4, else/otherwise enter the word ‘OK.’ Copy this formula from B4 to B5 through the end of your spreadsheet. Now, you would easily be able to see who needs to have invoice reminders sent out to them versus looking at each customer individually. IF statements are an excellent tool to save time and to ensure that you do not miss a cell.
Let’s look at one more example.
In the above example, the IF function in D2 is saying IF(C2 Is Greater Than B2, then return "Over Budget," otherwise return "Within Budget"). Below is how the Excel spreadsheet would look with the IF function and the data in the cells.
Before you begin the discussion this week, please review the following video. This video and article will provide a tutorial for the IF statements directly from Microsoft Support.
IF function (0:57 min, 381 words)
https://support.office.com/en-ie/article/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2?ui=en-US&rs=en-IE&ad=IE
Prompt
Now, it is your turn. We have learned that IF statements save time and also eliminate the human error of missing information in certain cells.
For this discussion, you get to illustrate a productive way you could use an IF statement in a spreadsheet for Joe's Ice Cream Truck.
Create an Excel spreadsheet with sample data of something that you would want to see for the business. Make the spreadsheet simple and post the spreadsheet with the data as your initial post along with your IF statement.