Imagine and on the rise, regional sales manager wishes to quickly see which store is exceeding its sales schools, 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 today total is shown for Bogota 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 greater than 150,000, 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 and the I column is true, greater than 150,000, the corresponding J cell reports exceeded goal. If the number and 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 mystical report. How can we make this easier to read, and more representative of what we were 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>=150,000,"Exceeded goal",I37<150,000,"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 and 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.
