Through his training and coaching programs, he has worked directly with thousands of aspiring Airbnb hosts to jumpstart their short term rental journeys. His works have been cited or featured on the Wall Street Journal, Reuters, Forbes, CNBC, Entrepreneur, and SKIFT. Symon He is the co-founder of LearnBNB, a leading online educational destination for all things Airbnb hosting and short-term rentals. Is my data valid based on certain conditions?Īs you become more familiar with Excel and the IF function, you’ll discover new uses specific to your domain and needs.What is the nth largest/smallest number in a list?.In combination with other functions and techniques, you can use the IF function to help answer questions based on your data set. The examples above are only some of the things you can use the IF function for. Here, we are labeling car manufacturers who are from Japan. The OR function in the second example requires only one of the conditions inside it to be true for the entire expression to evaluate to true. Here, A1 must be between 9 and 12, inclusive, which in this case represents the student’s grade level. The AND function used in the first example requires that all of the conditions inside the AND function be true for the entire expression to evaluate to true. In Excel, there are also logical operator formulas we can use with the IF function to allow us to check multiple conditions simultaneously. IF(OR(A1 = "Toyota", A1 = "Honda", A1 = "Mitsubishi", "Japanese Car", " ") If we had data that looked like this, what would the formula in the Category column look like? STORE Three examples of using the IF function Segmenting datasets using the IF functionĪ simple and frequent use case for the IF function is to identify subsets of a given dataset that meet criteria and label that data.įor example, if we have a list of student scores on a pass or fail exam where scores = 70 is a PASS, we can use an IF function like this: IF( SCORE 5% gets a HIGH PERFORMER label. However, you can use the IF function for so much more. In the simple examples above, we used the IF function to return a calculated numeric value. Here, since x is not greater than 3, the condition evaluates to FALSE, and the IF formula returns the value_if_false, which in this case is x / 3 or 1 / 3 = 0.333333333. Here, since x is greater than 3, the condition evaluates to TRUE, and the IF formula returns the value_if_true, which in this case is x * 3 or 6 * 3 = 18.
So, what the value x is 6, what is the result? IF( 6 > 3, 6 * 3, 6 / 3 ) = 6 * 3 = 18 If x is greater than 3 is TRUE, then return the value x times 3, else if x greater than 3 is FALSE, then return the value x divided by 3. You can use any of these comparison operators in the condition statement: If the values were 6 and 3, then 6 > 3 would evaluate to TRUE. For example, A1 > B1 is a logical expression that checks if the value in cell A1 is greater than the value in cell B1.
If it does not meet the condition, the third parameter you used in the function will be displayed.Ī common conditional used in IF statements are expressions to check the relationship between two values in your spreadsheet. If the condition is true, then the value in the second parameter will appear in the cell.
#If then formula excel how to
Reading it as an IF-THEN statement can help you understand how to use this function. The condition must be a value or a logical function that evaluates to either TRUE or FALSE. The syntax of the IF statement in Excel is as follows: IF(condition,, ) | By Symon He, Travis Chow Explore Course Understanding the IF function Packed with supplemental material for analysts. Master or review Excel with 10 real world case studies.