Boolean Operators Missing From Cell Expressions In Excel?

'Daniel Bowen' on Thu, 31 Aug 2006 05:11:27 GMT, sez:

=IF(ABS(B12-1993)<=7,1,0) :-)

'lb' on Thu, 31 Aug 2006 05:44:20 GMT, sez:

top answer Dan!

'Kalpesh' on Thu, 31 Aug 2006 09:23:41 GMT, sez:

It seems excel has a different way of "AND"ing the conditions

=IF(AND(A1>=1986, A1<=2000), 1, 0)

'Jody' on Thu, 31 Aug 2006 09:43:20 GMT, sez:

Daniel's solution is elegent; however it isn't obvious from looking at it that it does the logic you asked; what if you wanted to change the years? You would have to do a bit of math in order to update the formula.

Here one solution:
=IF(A1 >= 1986, IF(A1<=2000,1,0), 0)

A user looking at that formula can easily see what values needs to be changed.

'Simon' on Fri, 01 Sep 2006 14:19:31 GMT, sez:

Excel doesn't have any operators. All it has is *functions* that are named like operators. Once you figure that out, it will make sense AND(StopTryingInExcel)

'googlexcel' on Sat, 02 Sep 2006 23:17:20 GMT, sez:

google spreadsheet does it the same way...

'Nick Hodge' on Sun, 03 Sep 2006 23:25:45 GMT, sez:

Is A1 a date?

Oh my gawd I hope not, otherwise your poor elderly aunt is going to end up in a loony bin

'Calybute' on Sun, 09 Sep 2007 07:01:46 GMT, sez:

Try this...

=IF(A1<1986,0,IF(A1<=2000,1,IF(A1>2000,0)

It works and is it simpler than IF(AND)s to understand...

'Calybute' on Sun, 09 Sep 2007 07:03:43 GMT, sez:

Actually, that is essentially Jody's, written in longhand... but I found it easier to read....

'greg' on Wed, 28 Oct 2009 11:56:57 GMT, sez:

Excel will evaluate the function to boolean.
=AND(A1>=1986,A1<=2000)

To coerce the TRUE/FALSE to 1,0 just multiply by 1 or use the double unary --.

=--AND(A1>=1986,A1<=2000)

=1*AND(A1>=1986,A1<=2000)

'Brian Minton' on Thu, 10 Jun 2010 22:52:45 GMT, sez:

Excel does not have boolean operators, but it does have *arithmetic* operators. Excel uses the convention 0 = FALSE, any non-zero number = true, so you can do something like this:

=IF((B12 >=1986) * (B12 <= 2000),"yes","no")

This is not exactly intuitive though, and in your particular example, since you only want a 1 or a 0, the if statement is not even strictly necessary, as you could do the following:

=(B12 >=1986) * (B12 <= 2000)

name


website (optional)


enter the word:
 

comment (HTML not allowed)

All viewpoints welcome. But the right to delete any post for any reason is reserved. Don't make me do it. Aim for constructiveness. Comments may be republished, emailed to your loved ones or printed and used as toilet paper. Also, I get particularly nasty on comment spam. It's not worth even trying to post comment spam here -- your html is escaped, and your links are given a rel='nofollow'. By attempting to post a comment, you understand that if the comment is considered spam, at my absolute discretion, your IP address may be used as the target of a prolonged distributed denial of service attack. Your electricity might suddenly stop working. Your car tyres will go mysteriously flat. You will suffer permanent hairloss. Your dreams will be filled with terrifying monsters. And in any case I reserve the right to record and publish your IP address.

 

TimeSnapper_{Know thyself!}

TimeSnapper is a life analysis system that stores and plays-back your computer use. It makes timesheet recording a breeze, helps you recover lost work and shows you how to sharpen your act.

 

NimbleText is a Powerful FREE Tool

Use it for:

• extracting data from text
• manipulating text
• generating code

It makes you look awesome. Use it right now! Go on! Hurry! Don't walk, run!