We sometimes want to be able to carry out Logical tests to check if a value is TRUE or FALSE within spreadsheet. We carry out these logical tests in our heads quite easily, but sometimes have difficulty in getting it right in a formula. In this blog I’m going to dissect the steps and try to make writing the formula simple.
When using the word ‘IF’, we ask ourselves what to do when it is correct and what to do when it is incorrect. The basic syntax (formula structure) is as follows; IF(TEST, TRUE, FALSE), this means the first section of the formula in the brackets is the test, followed by the action if it is correct, otherwise the action if it is incorrect.
In the extract below the test is to see if the value in cell A1 is greater than the value in cell B1. The formula for the test is entered in cell C1 as follows: IF(A1>B1,”A is Higher”,”B is Higher”)
By changing the values in either A1 or B1, the formula in C1 will display one of the 2 statements. To take this one step further, what if both values are the same? We can revise the formula to take into account of this by adding another IF statement;
IF(A1>B1,”A is Higher”,IF(A1=B1,”Both the Same”,”B is Higher”)
This is known as Nested IF, you can do up to 7 tests using the Nesting IF. The FALSE outcome must always be at the end, in other words if all the tests fail, what the result should be.
You can use AND function within your formula where you are checking certain cells meet a criteria, say for example you want to check to see if an age falls between 2 numbers say 18 and 25.
In the above example the syntax for the formula is AND(Test1,Test2….). You can have up to 255 tests, however, I would not recommend so many tests, no more than 4 would be my suggestion. With the AND function in order for the result to be TRUE, all the tests must be TRUE, if one of the test is FALSE then the result will be FALSE. Above you can see that Fred’s age falls into the 31-40 category as the value in cell B2 is TRUE for the formula AND(B2>31,B2<40).
The OR function is similar to the AND function above, but in this case you are looking to identify 1 test to be TRUE. The syntax for is OR(Test1,Test2…), once again you could test up to 255, but would suggest about 4.
Using the same example as the AND function and replacing that with OR you see the result of them all be being TRUE as one of the tests is TRUE. In the above example Fred’s age falls into each of the age categories, as the value in cell B2 meets at least one of the tests, i.e. its greater than 18, its greater than 26 and also greater than 31.
This is a strange one and I must admit that I have not used this in my time, but am aware of how it works. The NOT function reverses the value of the argument. If the Test is TRUE then the result is FALSE, and if the test is FALSE then the result is TRUE.
As you can see from the example above, A1+B1 does equal 24, but the result is shown as FALSE.
With all of these functions you can mix and match them to deal with some complex logical tests.
The example below is one that I did for a client recently.
=IF(AND(D2=””,C2<=TODAY()),”Problem“,IF(AND(D2=””,C2>TODAY()),”Okay“,IF(D2<=C2,”Approved“, IF(D2>C2,”Approved Late“,”Error“))))
I hope that this has been useful and to be honest it took me some time to get my head around the formula but once I did, I use them quite a bit in my work.