Tuesday, April 11, 2006

IF statements and conditional formats in Excel

I found this little trick yesterday while running a training session in Excel.
Normally if you want to build an IF statement in Excel that is conditional on 2 or more premises than you need to use the AND function to evaluate them.
Eg. =IF(AND(A4=A5,A4=64),TRUE,"you are an ID10T")
However if you want to evaluate the same information in a conditional format than you do not need to build the function the same way.
In fact you can simply go =IF(A4=A5,A4=64) and it will work. Alternatively you could use =AND(A4=A5,A4=64) to achieve the same result.
So not only do you not need to complete the IF statement with the TRUE and FALSE arguments (in effect that is what the conditional format is anyway) you also don't have to use the AND function and can still test multiple conditions.
This is quite a different syntax to the formula function syntax.
If you want to test for either condition than you just need to put =OR(A4=A5,A4=64).


Posted by: Jethro | 3:47 PM |