#Sheets if then formula how to
#Sheets if then formula series

IFS allows you to test multiple conditions and it returns the first answer that is true. If it’s true it returns “Detractor”, if it’s false then it performs the second check and based on whether that’s true or false returns “Promoter” or “Passive”.īut a better solution is to use the IFS function. The first statement checks if the number is 6 or less. You can solve this by nesting a second IF statement inside the first. However, because there are three categories (Detractor, Promoter and Passive) instead of just two, it’s not possible with a single IF statement. To do this in your spreadsheet, you need to convert the scores to the right categories like we did with our pass or fail example. Detractors are classified as anyone who gives you a rating of 6 or less and promoters as anyone with a score of 9 or 10. The score is then worked out by subtracting the number of detractors from promoters and dividing that by the total number of respondents. Customers are asked to score from 0 to 10 how likely they would be to recommend your company/product/service to a friend or colleague. NPS is a method to measure customer loyalty and product/service virality. In a slightly more complex example, imagine we want to use Google Sheets to calculate a Net Promoter Score (NPS). Here’s a list of the various conditions you can test:Ĭalculating NPS Net Promoter Score (NPS) in Google Sheets using the IF statement

#Sheets if then formula series

Imagine you have a series of test results and you want to classify them as pass or fail depending on whether they score higher than 80%.

The possibilities of this are endless, but here are some examples of what you can achieve. IF evaluates a logical expression and returns a different value depending on whether it’s TRUE or FALSE.

IFERROR is just the tip of the iceberg when it comes to what’s possible with Google Sheets’ Logical Functions. IFERROR checks the value for (C5/B5) -1, sees that the result is an error because you’re trying to divide by zero, and so returns the text “N/A”. We can replace #DIV/0 with ‘N/A’ (or anything else of our choosing) by updating the formula to =IFERROR((C5/B5 - 1),“N/A”). This returns #DIV/0 because no Kiwis were sold in March. The ‘MoM Change’ column for Kiwis contains the calculation: (C5/B5)- 1. If it doesn’t detect an error it just returns the original value. It checks a value for errors, and if it encounters one it replaces the value with whatever you specify. The solution is to use Google Sheets’ IFERROR() function. Others like #VALUE!, which occur when you try and perform a mathematical operation like multiplication or division using a cell that contains text, can often be circumvented by cleaning up your data first. Some errors like #DIV/0!, which happen if you try and divide a number by zero, aren’t easily avoidable. The bigger problem though is when errors prevent subsequent calculations from working elsewhere in your spreadsheet. Rather than displaying an ugly error in your data, you’d rather show a blank or a zero. These errors occur when you try to do a calculation that the spreadsheet can’t handle, and they can be a real pain. If you’ve been using spreadsheets for any serious amount of time, you’re bound to have encountered cell errors.

#Sheets if then formula how to

How to use the IFERROR function to handle errors in Google Sheets We promise, these functions will make your life easier when it comes to dealing with lots of data in a spreadsheet.ġ. Bye bye manual, tedious calculations! Using the right function for a calculation is a game changer.Ĭontinuing in our efforts to help you become a Google Sheets wiz, we’re sharing three more advanced Google Sheets functions you can start using today. If you work with numbers or any kind of data sets, you know how much time, energy, and frustration spreadsheet functions can save you.