how to create an if then formula in excel

HOW TO CREATE AN IF THEN FORMULA IN EXCEL FULL

To demonstrate the concept, let's check the item names in column A and return 'Fruit' for Apple or Orange and 'Vegetable' for Tomato or Cucumber: In situations when you want to test a few sets of OR criteria and return different values depending on the results of those tests, write an individual IF formula for each set of 'this OR that' criteria, and nest those IF's into each other. =IF(OR(EXACT(A2, 'AA-1'), EXACT(A2, 'BB-1')), 'x', '')Īs the result, only two orders IDs where the letters are all capital are marked with 'x' similar IDs such as 'aa-1' or 'Bb-1' are not flagged:įormula 4. In this example, let's find and mark the order IDs 'AA-1' and 'BB-1': IF(OR(EXACT( cell,' condition1'), EXACT( cell,' condition2')), value_if_true, value_if_false) In this case, perform each individual logical test inside the EXACT function and nest those functions into the OR statement. However, your data might be case-sensitive and so you'd want to run case-sensitive OR tests.

The screenshot below shows both formulas in action:Īs already mentioned, the Excel OR function is case-insensitive by nature.

HOW TO CREATE AN IF THEN FORMULA IN EXCEL FULL

So, you use the OR function to check both conditions, and if the result is TRUE, decrease the total amount by 10% (B2*C2*0.9), otherwise return the full price (B2*C2):Īdditionally, you could use the below formula to explicitly indicate the discounted orders:

Unit Price in C2 is greater than or equal to $5.

in B2 is greater than or equal to 10, or.

multiplied by Unit price) and you want to apply the 10% discount if either of these conditions is met: Say, you calculate the total amount for an order ( Qty. Looking for a formula that can do something more complex than return a predefined text? Just nest another function or arithmetic equation in the value_if_true and/or value_if_false arguments of IF. If a cell is this OR that, then calculate The task can be accomplished with the following IF OR function:

In Excel 2003 and lower, you can use up to 30 arguments, and a total length shall not exceed 1,024 characters.Īs an example, let's check columns A, B and C for blank cells, and return 'Incomplete' if at least one of the 3 cells is blank.

In Excel 2016 - 2007 formulas, up to 255 arguments are allowed, with a total length not exceeding 8,192 characters.

There is no specific limit to the number of OR conditions embedded into an IF formula as long as it is in compliance with the general limitations of Excel: If you'd like to distinguish text case, wrap each argument of the OR function into EXACT as shown in this example.īelow you will find a few more examples of using Excel IF and OR functions together that will give you more ideas about what kind of logical tests you could run. In our case, 'delivered', 'Delivered', and 'DELIVERED', are all deemed the same word.

Please pay attention that an IF OR formula in Excel does not differentiate between lowercase and uppercase characters because the OR function is case-insensitive.

How to create an if then formula in excel