I need to know how to write the following IF() statement in Excel. Yes, I once knew Excel very well, but they HAD to change the syntax to something less logical. Now, I can’t write a simple ()IF statement!
Here is what I am trying to do:
IF B2 OR C2 =1, THEN 1 ELSE 0
As I recall, it used to be something like =IF(((B2 OR C2)=1),1,0)
…or, it was =IF(((B2=1) OR (C2=1)),1,0)
Nowadays, neither one shown above is correct. And, as I recall, one yielded a NAME? error. The other gave me a pop-up box saying that my formula has an error.
I think that’s just because these are all implemented as functions, not operators.
I mean, that does invite the question why. The regular mathematical operators are still operators; you don’t need to do PLUS(A1, B1) to add two things together.
You can use a double negative in front of a logical function to transform the output into 1s and 0s rather than TRUE or FALSE. E.g:
OR(B2,C2) returns TRUE or FALSE but
–OR(B2,C2) returns 1 or 0.
Saves the need to use IF statements at all.
B2
C2
- -OR(B2,C2)
0
0
0
0
1
1
1
0
1
1
1
1
On edit: Discourse turns a double negative into an em-dash. You want “- -” but without the space.
No, they did not change the syntax. You remember wrongly.
Other than adding new functions, the expression syntax in today’s latest version is the same as it was in Excel v1.0 back in the late 1980s.
Your English formula is wrong or at least ambiguous. You wrote:
IF B2 OR C2 =1, THEN 1 ELSE 0
Does that mean to compare B2 to 1 and also compare C2 to 1 and if either comparison comes out equal then produce a one and otherwise produce a zero? Or did you mean produce a one if C2 equals 1 or if B2, when converted to a TRUE / FALSE value equals TRUE, and otherwise produce a zero?
Both of those interpretations are perfectly cromulent English, but produce very different results in math & logic.
So which is it?
If you meant the former, here’s another way to write the same thing that might seem familiar to you. =IF(B2=1,1,IF(C2=1,1,0),0)
I typed a whole bunch saying this but was afraid I might be remembering wrong. I’m pretty sure the current syntax was also the way it was in Lotus 1-2-3 and maybe even VisiCalc (very unsure about VisiCalc, it had a lot fewer functions)
Yes, it’s maddening that Excel never had, and never will, have logical operators. Those functions are ugly, and very difficult to debug when they get complex.
One alternative is to use comparisons to return numbers, and a zero evaluates to FALSE, and a non-zero evaluates to TRUE.
So, for example (just looking at Color for now)
Type
Color
Formula
Value
Value (=D2+0)
Bicycle
Blue
'=B2=“Blue”
TRUE
1
Bicycle
Red
'=B3=“Blue”
FALSE
0
Putting it together, we now compare Type and Color
Type
Color
AND
OR
Bicycle
Blue
This is TRUE
This is TRUE
Bicycle
Red
Nope, FALSE
This is TRUE
Car
Red
Nope, FALSE
Nope, FALSE
Where the formula in the AND column is
=IF((A2=“Bicycle”)*(B2=“Blue”),“This is TRUE”,“Nope, FALSE”)
And the OR is
=IF((A2=“Bicycle”)+(B2=“Blue”),“This is TRUE”,“Nope, FALSE”)
An exercise to the Reader is to implement NOT, XOR, NOR
IMO they decided against it as a way of preventing exactly the issue I pinged the OP about. This is a tool for secretaries and sales managers, not software developers. It needs to be more idiot-proof than it needs to be easy.
This is EXACTLY why I failed Pascal…the STUPID syntax! I’m sorry, but it must be said the computer automatons HAVE to find the hardest way to do the simplest things. I guess it is job security. I’ve even tried to explain such matters to them, and you can argue until you’re blue in the face, and they just don’t get it! Even Mr. Spock would be annoyed at their illogical logic.
Something doesn’t jive here. I once derived and programmed speciifc astronomy programs for events at my location without an issue as far as the use of Excel functions. Likewise, I programmed spreadsheets to calculate convection losses across vertical and horizontal plates for various runs of rectangular metal duct work. Again, no issues with the commands…on a PC. Unless MS made Excel for Mac a little differently?
IANA Mac expert. But the worksheet files transfer back and forth between PCs & Macs just fine.
I’d sooner believe that you have misremembered how you used to do something. That’s sure teh way to be with most people, myself certainly included.
After enough years decades using enough different software packages on enough different systems it’s real easy to think that ABC worked like this and DEF worked like that 15 years ago when reality was different.
You still haven’t told any of us how you think it works or worked.
That’s hilarious, I haven’t seen that. I took an AI course in college that used LISP for programming exercises and haven’t touched it since, but that’s something you don’t forget.
Short-circuiting is mostly a performance optimization. VBA and performance don’t really belong in the same sentence together.
One ought not be constructing conditionals including calls to methods with side effects. So semantically there should be no difference between conjunctions with or without short-circuiting. Including calls with side effects in conditional evaluations is usually a sign of an unhealthy addiction to terseness over clarity and correctness.
Oh yeah, almost forgot … …
Harrumpph! Damn kids these days leaving footprints on my lawn!
