April 27, 2004

Logic Functions

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").

A bit complex but practice makes perfect :)

Andrew of 2005 says "When using Conditional Formatting, keep in mind that only a TRUE condition is required for it to take effect. For Data Validation (custom), the input must be TRUE to be allowed."

Write a Comment
So, with all of your vast Excel knowledge, here's a question for you. I'm trying to average bedtimes in Excel and there are bedtimes before and after midnight. All of my times are coded in military time. Is there an easy way to average time since Excel won't let you display negative times (I tried subtracting the times before midnight from midnight to get the difference and then make that negative to in order to have an average around midnight.) If you have ever tried to do this, please let me know how to get Excel to function this way else I'll have to recode by hand.

Thanks so much!
Casper
Posted by Casper at November 04, 2005 09:48
Hi Casper,

Still there? I've been in need of bedtime myself :-)

Here's one way to work out hours where dates are not used. Cell A1 is bedtime and Cell A2 is wake up time,

=(DAY(A2)-DAY(A1))*24+HOUR(A2)-HOUR(A1)

This seems to work when dates are used. Sure there are other ways too.

=(DAY(A2)-DAY(A1))*24+(HOUR(A2)-HOUR(A1))

Hope this helps.
Posted by Andrew at November 07, 2005 19:46