Pro Tip: get comfortable using
BOOLEAN results in formulas. You can go a long distance in a complicated spreadsheet using simple booleans for control.
One of the simplest ways to make a spreadsheet complex is to start adding some logic with simple logic formulas. The logic functions do some evaluation and return a
FALSE. Sometimes the result is useful but typically you will then feed that result into
IF to get a different result depending on the previous result. One thing that is not always obvious to people is that you can have Excel work with boolean values directly. An example:
=IF(A1>1, TRUE, FALSE) ...even worse =IF(A1>1, 1, 0)
I’ve seen that type of formula countless times. The user wants to keep track of some state based on logical functions but uses
IF to return the values. The simpler version of above would be:
It looks a little odd at first but Excel will interpret that formula as wanting to return a boolean value. Your cell will then contain
FALSE. Depending on what you want to do with the result, you can use the boolena directly or with modification. The boolena can be used directly in an
IF in a different cell. You can also use them for math; Excel typically treats
The most useful logic functions I use:
ISERRORare the same except for how they handle
#N/A!. I never use
ISERRsince I want
#N/A!to be called an error.
FALSEif you are missing the arguments. That is,
FALSE. Of course at that point, you should just drop the if. Sometimes this is useful if want a default value for one or the other.