Using IF and IFS to analyze data
1. Using IF and IFS to analyze data
Person: Imagine an on-the-rise regional sales manager wishes to quickly see which store is exceeding its sales goals and which is not by looking at the sales to date in a Google Sheet. The sales manager has a spreadsheet with several columns-- City, State/Province, Country, Region, Month, Product, Sales - Actual, Sales - Forecast, and Sales to date. The manager adds a tenth column, Goal Status, and creates a formula using the IF function to display when a store is exceeding their sales goal or when they have missed their sales goal for the first six months as shown in the Sales to date column, column I. Our first sales to date total is shown for Bogotá, Colombia, in cell I37. So let's add our formula to cell J37. The formula is written as: =IF(I37 >150000, "Exceeded Goal", "Missed Goal"). Let's break down the formula to get a better understanding of how it will work. After calling the IF function, the formula references cell I37, which contains the sales to date, year to date result for the first store. The regular expression is completed with ">150000" which tests the number in the cell against the goal sales dollars of $150,000. If you copy the formula down the J column, the results are displayed. If the number in the I column is true-- greater than 150,000-- the corresponding J cell reports "Exceeded Goal." If the number in the I column is false-- less than 150,000-- the corresponding J cell reports "Missed Goal." Are these results helpful? The sales to date calculation is only in some of the cells, not all of the cells. An empty cell is less than 150,000, so each empty cell has a "Missed Goal" report. How can we make this easier to read and more representative of what we are trying to learn about each store? Let's add another "Goal Status" column and use the IFS function to make the information more useful. We still want to know if a store has met or exceeded its sales target, so we will use the same approach. If the sales to date value is equal to or greater than $150,000, the store has exceeded its goal. If the sales to date value is less than $150,000, the store has missed its goal. But there is an additional factor we would like to consider: what happens if there is no information in the cell? You want the response to be a blank cell as well. Using the IFS function, you can consider all of these options. The IFS statement is written in this way: =IFS(I37 ="", "", I37 >=150000, "Exceeded Goal", I37<150000, "Missed Goal") Note that the formula considers the operations in order. Since an empty cell is less than $150,000, this operation should go first to ensure that when a cell is blank or empty, the formula will report a blank cell. When we copy the formula in column K, we can see there are now three options reported about how well the stores are doing related to their goals. This information will be more helpful to a sales manager who is quickly trying to determine if there needs to be any support given to a store because they have missed their sales goal.2. Let's practice!
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.