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 BOOLEAN value, TRUE or 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:
=A1>1
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 TRUE or 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 FALSE as 0 and TRUE as 1.
The most useful logic functions I use:
=< > <= >=IS functions: ISTEXT, ISNUMBER, ISERRORNOT(ISERROR(MATCH()))Extra details:
ISERR and ISERROR are the same except for how they handle #N/A!. I never use ISERR since I want #N/A! to be called an error.IF will return TRUE or FALSE if you are missing the arguments. That is, IF(SOME_CONDITION,,) or IF(SOME_CONDITION) will return TRUE or 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.