Boolean Operators Missing From Cell Expressions In Excel?
(how's that for a geeky title)
Okay, the simple excel function i wanted to teach someone to write:
"If A1 is between 1986 and 2000 (inclusively) then print '1', otherwise print '0' "
How would you write that as a cell function?
I haven't done much excel lately. Here's how I thought it would be written:
=IF(B12 >=1986 AND B12 <= 2000,1,0) <-- Nope!!!
=IF(B12 >=1986 && B12 <= 2000,1,0) <-- Uh-uh!! No siree!!
(i was still refusing to look up help at this point...)
Forget it... i'll nest the logic...
=IF(B12 >=1986, IF(B12 <= 2000), 1 ,0) ,0) <-- Yes this worked. But...
Yuck. I mean... honestly!!
So I tried...
=IF(B12 BETWEEN (1986, 2000), 1, 0) <-- Doesn't work
=IF(B12 IN (1986...2000), 1, 0) <-- Doesn't work
(also tried with square brackets, curly brackets, angle brackets, pipe symbols, the phoenician alphabet, a donkey's leg, a bat's wing....)
Looked in help... and oh my god how lispy is this:
=IF(AND(B12 >=1986, B12 <= 2000), 1, 0)
How am i supposed to teach my frail elderly aunt to grok that?
It's elegant from the computer's point of view... it's an expression tree... i'd enjoy writing a program to parse that, i know i would... but teaching a regular human to read it, use it, debug it, maintain it, trust it, tweak it, share it, co-opt it, .... never.
This is a symptom of excel decimating the competition a long time ago and then stagnating. Yes: the dodo effect.
If excel were a version 2 product, I'd say "hey that's pretty good", but we're talking about version 11 here. They should've allowed cell functions like this: "= IF (B12 >=1986 AND B12 <= 2000, 1, 0) " long long ago. Operators... operators... not just functions. Lisp had it back in 1902. ah forget it. I wonder how wikiCalc handles cell functions? or spreadsheets.google.com?Next → ← Previous
I'm currently writing a book about how to build your first product. If you want to build your first product, please sign up to be notified when the book is available.