The formula I have written for the STATUS column (Illustration below) is =IF(OR(L2>=70,M2>=70,N2>=70),"Y","N") Show Problem is...If all cells are blank, it returns N (Correctly). Here is a visual: Any help would be greatly appreciated. ReplyVeere van Keulen says: Hello, The message that pops up when I try to run the formula for multiple options ( =IF(OR('Sheet1'!C3="Option1",'Sheet1'!C3="Option2") ;'Sheet1'!A3;"") ) is the following: "There's a problem with this formula, Not trying to type a formula When the first character is an equal ("=") or ("-") sign, Excel thinks it's a formula. " I use "=" before the formula and I have checked the # of brackets, "" etc. I do have to mention that the options (here "Option1" and "Option2") come from a picklist. I have checked whether spelling mistakes or absence of the option in the picklist might me the problem, but that is not the case. The results that I would like to get in this formula would be as follows: - scenario 1 (TRUE): option 1 (a specific department of the company) is chosen in the picklist in 'Sheet1'!C3 --> then 'Sheet2'!A3 should contain the same value as in 'Sheet1'!A3 (which would be someones name) - scenario 2 (TRUE): option 2 (another specific department of the company) is chosen in the picklist in 'Sheet1'!C3 --> then 'Sheet2'!A3 should contain the same value as in 'Sheet1'!A3 (someones name) - scenario 3 (FALSE): another option (another department) is chosen from the picklist in 'Sheet1'!C3 (neither option 1 or 2) --> then 'Sheet2'!A3 should stay empty. I hope this information helps! ReplyVeere van Keulen says: Hello, When I try this formula =IF(OR('Sheet1'!C3="Option1",'Sheet1'!C3="Option2") ;'Sheet1'!A3;"") I get the following message: "There's a problem with this formula. Not trying to type a formula?When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula " I have checked the brackets, "", = signs and the spelling of the options in the formula (here shown as "Option1" and "Option2") and these should not be the problem. The IF function allows you to make a logical comparison between a value and what you expect by testing for a condition and returning a result if True or False.
So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False. IF statements are incredibly robust, and form the basis of many spreadsheet models, but they are also the root cause of many spreadsheet issues. Ideally, an IF statement should apply to minimal conditions, such as Male/Female, Yes/No/Maybe, to name a few, but sometimes you might need to evaluate more complex scenarios that require nesting* more than 3 IF functions together. * “Nesting” refers to the practice of joining multiple functions together in one formula. Technical detailsUse the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. Syntax IF(logical_test, value_if_true, [value_if_false]) For example:
Argument name Description logical_test (required) The condition you want to test. value_if_true (required) The value that you want returned if the result of logical_test is TRUE. value_if_false (optional) The value that you want returned if the result of logical_test is FALSE. RemarksWhile Excel will allow you to nest up to 64 different IF functions, it’s not at all advisable to do so. Why?
If you find yourself with an IF statement that just seems to keep growing with no end in sight, it’s time to put down the mouse and rethink your strategy. Let’s look at how to properly create a complex nested IF statement using multiple IFs, and when to recognize that it’s time to use another tool in your Excel arsenal. ExamplesFollowing is an example of a relatively standard nested IF statement to convert student test scores to their letter grade equivalent. 
This particular example is relatively safe because it’s not likely that the correlation between test scores and letter grades will change, so it won’t require much maintenance. But here’s a thought – what if you need to segment the grades between A+, A and A- (and so on)? Now your four condition IF statement needs to be rewritten to have 12 conditions! Here’s what your formula would look like now:
It’s still functionally accurate and will work as expected, but it takes a long time to write and longer to test to make sure it does what you want. Another glaring issue is that you’ve had to enter the scores and equivalent letter grades by hand. What are the odds that you’ll accidentally have a typo? Now imagine trying to do this 64 times with more complex conditions! Sure, it’s possible, but do you really want to subject yourself to this kind of effort and probable errors that will be really hard to spot? Tip: Every function in Excel requires an opening and closing parenthesis (). Excel will try to help you figure out what goes where by coloring different parts of your formula when you’re editing it. For instance, if you were to edit the above formula, as you move the cursor past each of the ending parentheses “)”, its corresponding opening parenthesis will turn the same color. This can be especially useful in complex nested formulas when you’re trying to figure out if you have enough matching parentheses. Additional examplesFollowing is a very common example of calculating Sales Commission based on levels of Revenue achievement.
This formula says IF(C9 is Greater Than 15,000 then return 20%, IF(C9 is Greater Than 12,500 then return 17.5%, and so on... While it’s remarkably similar to the earlier Grades example, this formula is a great example of how difficult it can be to maintain large IF statements – what would you need to do if your organization decided to add new compensation levels and possibly even change the existing dollar or percentage values? You’d have a lot of work on your hands! Tip: You can insert line breaks in the formula bar to make long formulas easier to read. Just press ALT+ENTER before the text you want to wrap to a new line. Here is an example of the commission scenario with the logic out of order: Can you see what’s wrong? Compare the order of the Revenue comparisons to the previous example. Which way is this one going? That’s right, it’s going from bottom up ($5,000 to $15,000), not the other way around. But why should that be such a big deal? It’s a big deal because the formula can’t pass the first evaluation for any value over $5,000. Let’s say you’ve got $12,500 in revenue – the IF statement will return 10% because it is greater than $5,000, and it will stop there. This can be incredibly problematic because in a lot of situations these types of errors go unnoticed until they’ve had a negative impact. So knowing that there are some serious pitfalls with complex nested IF statements, what can you do? In most cases, you can use the VLOOKUP function instead of building a complex formula with the IF function. Using VLOOKUP, you first need to create a reference table:
This formula says to look for the value in C2 in the range C5:C17. If the value is found, then return the corresponding value from the same row in column D.
Similarly, this formula looks for the value in cell B9 in the range B2:B22. If the value is found, then return the corresponding value from the same row in column C. Note: Both of these VLOOKUPs use the TRUE argument at the end of the formulas, meaning we want them to look for an approxiate match. In other words, it will match the exact values in the lookup table, as well as any values that fall between them. In this case the lookup tables need to be sorted in Ascending order, from smallest to largest. VLOOKUP is covered in much more detail here, but this is sure a lot simpler than a 12-level, complex nested IF statement! There are other less obvious benefits as well:
Did you know?There is now an IFS function that can replace multiple, nested IF statements with a single function. So instead of our initial grades example, which has 4 nested IF functions:
It can be made much simpler with a single IFS function:
The IFS function is great because you don’t need to worry about all of those IF statements and parentheses. What are the 3 arguments of the IF function in Excel?IF is one of the Logical functions in Microsoft Excel, and there are 3 parts (arguments) to the IF function syntax: logical_test: TEST something, such as the value in a cell. value_if_true: Specify what should happen if the test result is TRUE. value_if_false: Specify what should happen if the test result is FALSE.
Can IF statement have 3 conditions?You can use the following formulas to create an IF function with 3 conditions in Excel:. Method 1: Nested IF Function =IF(C2<15, "Bad", IF(C2<20, "OK", IF(C2<25, "Good", "Great"))). Method 2: IF Function with AND Logic =IF(AND(A2="Mavs", B2="Guard", C2>25), "Yes", "No"). Which of the following is an argument for the IF function?IF Formula
The function uses the following arguments: Logical_test (required argument) – This is the condition to be tested and evaluated as either TRUE or FALSE. Value_if_true (optional argument) – The value that will be returned if the logical_test evaluates to TRUE.
What is the first argument of an IF function?The first argument of the IF() function is a logical test. In this example, we want to flag a value in cell B5 (Revenue) if it is above 15000 and less than 20,000. This is a typical situation when we can use the AND() logical function to unite several certain conditions into a single logical test.
|
