QUESTION :
The following formulas work individually but when I combine them I get a VALUE error, please help!
=IF(AND(LEFT(D7,14)=”Cost Avoidance”)*AND(AI7>=100000),”Yes, provide validation form in column AK”,”No”)
=IF(AND(LEFT(D7,14)=”Cost Reduction”)*AND(AI7>=50000),”Yes, provide validation form in column AK”,”No”)
Combined (doesn’t work): =IF(LEFT(D7,14)=”Cost Avoidance”*AND(AI7>=100000),”Yes, provide validation form in column AK”,IF(LEFT(D7,14)=”Cost Reduction”*AND(AI7>=50000),”Yes, provide validation form in column AK”,”No”))
ANSWER :
IF( AND( expression; expression2; expression3; ...) ; "All true"; "All false")
To get “help” on any function, e.g. for AND:
In Excel,
– press F1,
– then find the /Search -tab and
– enter AND in the search field,
– press ENTER,
– find AND (function) and click on it…
– read the text.
From memory, allow for small differences to the above.
NOTE: ; or , between values – depends on your locale settings.
=IF(AND(LEFT(D7,14)=”Cost Avoidance”,AI7>=100000),”Yes, provide validation form in column AK”,IF(AND(LEFT(D7,14)=”Cost Reduction”,AI7>=50000),”Yes, provide validation form in column AK”,”No”))
Solved it on my own
Hannu’s answer covers it but if you’re new to Excel, the syntax might seem odd. I’ll expand the explanation.
The AND syntax is:
AND(condition1,condition2,...)
The various conditions that must all be true are a list inside AND, separated by commas. AND returns a value of TRUE if all the conditions in the list are true, and FALSE otherwise. So your two AND expressions would look like:
AND( LEFT(D7,14)="Cost Avoidance"), AI7>=100000 )
AND( LEFT(D7,14)="Cost Reduction"), AI7>=50000 )
Each AND expression returns a value of TRUE or FALSE, so the entire expression can be used in places where you want to make a decision based on some condition or conditions being TRUE or FALSE.
The IF function tests whether something is TRUE or FALSE, and returns a result for each case:
IF( condition, result_if_TRUE, result_if_FALSE)
That condition can be your entire AND expression.
From your description, you have two different AND expressions, and you want the same set of TRUE/FALSE results if either is true. You can use OR for that. Like AND, the syntax is:
OR(condition1, condition2,...)
You give it a list of conditions. OR returns TRUE if any one or more of the conditions in the list is TRUE, and FALSE if none are true.
Those conditions can be anything that returns a TRUE/FALSE result, including your AND expressions. So each AND expression can be a condition in the OR list. You get:
OR( AND(LEFT(D7,14)="Cost Avoidance"),AI7>=100000), AND(LEFT(D7,14)="Cost Reduction"),AI7>=50000) )
This entire OR expression produces a TRUE/FALSE result, so it can be used as the condition in your IF test:
IF( OR(AND(LEFT(D7,14)="Cost Avoidance"),AI7>=100000),AND(LEFT(D7,14)="Cost Reduction"),AI7>=50000)), "Yes, provide validation form in column AK", "No" )
All of this depends, of course, on your question accurately describing what you want to do. Also, be aware that the list and argument separators can vary by locale. In some locales, a semi-colon is used rather than a comma. If you use the function wizard to help build your formula, it will insert the correct separator character.