<$BlogRSDURL$>

April 27, 2004

Logic Formulas
To the best of my knowledge there are just 6 logic functions within Excel. But these 6 functions should be all that you will ever need.

IF
AND
OR
NOT
TRUE
FALSE

I would like to have a look at the first four of these functions. (To be honest, I don't think the TRUE and FALSE functions are particularly useful by themselves. Also please note that you can combine these functions to make custom made functions such as NOR by using NOT and OR, or NAND using NOT and AND)

IF
This is definitely one of the most useful functions in Excel. It basically works like this, =IF(criteria is true, TRUE, FALSE). For example, if in cell A1 you have the number 10, =IF(A1=10,TRUE,FALSE) will give you TRUE. If I changed the number in A1 to 9, the same function will give me FALSE because A1 does not equal 10.

In addition to the equal sign (=), you can also use greater than (>), less than (<), or make combinations such as <=, >=, and <>. Text is also recognized so formulas such as =IF(A1="ten",TRUE,FALSE) will give me TRUE if the A1 cell had been entered as 'ten' instead of just 10. Upper case or lower case is not affected, you could even write TeN or tEn and still get the same result. Note that numbers, cell references, TRUE and FALSE do not require quotation marks.

You can also change the TRUE and FALSE response to something like =IF(A1="","Please enter something","Thanks"). The double quotation marks with nothing between them mean that the A1 cell is blank.

Finally, you can use equations and other functions combined such as =IF(A1=100,A1+20,IF(A1>100,A1+30,"Less than 100")).

This is an example of a nested IF formula, you can use up to seven IFs together in the same formula, just don't forget to add extra brackets.

AND
You can use this formula by itself to get TRUE or FALSE. It's a multicriteria function, you can add up to 30 criteria that must all be correct to get TRUE. Here's an example - =AND(A1=12,B1="John") will give you TRUE only if both 12 and 'John' are entered into cells A1 and B1.

OR
This is another multicriteria function (up to 30 criteria just like AND). Use this to choose at least one criteria. For example, =OR(A1=25,A1=40,B1=75) will give you TRUE if at least one of the criteria are met. Notice that I used cell A1 twice. You can multi-query the same cell with OR but not AND. There is no way that a single cell can have more than one value at the same time.

NOT
NOT seems to be applicable to just one criteria at a time. No problem, you can use it together with IF, AND or OR, such as in =NOT(OR(A1=32,B1=60,C1="Overdue",D1=E1+F1)) or perhaps =IF(AND(A1>24,OR(NOT(B1>100),C1=TRUE),D1="Paid"),"Great!","Try again").

Complex? Practice makes perfect :)

This page is powered by Blogger. Isn't yours?