 |
|
|
|
As you become more proficient with Excel and it's formulas you will most likely use what is called Nesting. This means that you are using the result of one formula as the argument in another. For instance you may want count all the cells in the range A1:A10 that are equal to the SUM of range B1:B10, so you could use:
=COUNTIF(A1:A10,SUM(B1:B10))
In this case we have used the result of the SUM function as our second argument in the COUNTIF function, so we have nested the SUM function within the COUNTIF function. We can nest a function within any function that takes an argument, but the result of that function must return the same type of result the argument uses. In other words we could not nest a function that only returns Text into the argument of another function that must have a number.
There is also a limit to the amount of levels we can nest functions within each other and that limit is seven.
By far the most commonly used function in Excel is the IF function. But it is also nearly always used with another function nested within it. There are five other types of logical functions and they are AND, NOT, FALSE, TRUE and OR. As with the IF function these are generally used with another function nested within them, or they themselves are nested within a function. There is nothing complicated about the IF function it simply returns TRUE or FALSE as it's result.
=AND(A1=10,A2=20)
Would return TRUE only if both A1=10 and A2=20. Any other combination would return FALSE. It is very unlikely you would use the AND function on it's own like this. You would normally nest it within an IF function, like this:
=IF(AND(A1=10,A2=20),"Yes","No")
This says, if A1=10 and A2=20 then say Yes otherwise say No.
The NOT function can be used to reverse the result of the IF or another logical function, eg;
IF(NOT(AND(A1=10,A2=20)),"Yes","No")
In this case if both A1=10 and A2=20 the result would be No.
The OR is very similar to the AND except that while the AND requires both arguments to be TRUE in order to return TRUE, the OR only needs one argument to be TRUE for it to return TRUE. Both the AND and the OR function can except no less than two arguments and no more than 30.
You will find yourself using the logical functions a lot in Excel to determine whether a condition(s) is/are TRUE and then acting accordingly.