Microsoft Office Tutorials and References
In Depth Information
Let’s Be Logical
OR, shown previously in the chapter, returns TRUE when at least one
condition is true. This makes sense considering it ‘s the word or — as in “this
or that.” So what does XOR mean? In logic speak XOR is an acronym for
Exclusive Or. With a given set of conditions XOR returns FALSE if all the
conditions are true. This can be confusing! Please don’t shoot me — I’m just the
Seriously, you really must wonder what is going on here. I have always found
that the best way to think of XOR is that it does the opposite of OR. In other
words, if only one condition needs to be true in an OR test to have it return
TRUE, then if only one condition in an XOR test is true it returns FALSE.
Believe it or not there are useful applications for this.
Figure 14-6 shows a worksheet that compares the percent of change in
revenue, compared month to month over a three-year span. For example, Feb
2011 had an increase of 9 percent over the same period from the previous
year. Feb 2012 has an increase of 11 percent over the same period in 2011,
and Feb 2013 had an increase of 16 percent over the period of Feb 2012. In a
nutshell, revenue has been increasing each February compared to the
previous February. This is the type of news that makes business manager types all
tingly and ready to go out dancing.
The revenue percent change is shown for all 12 months over the three-year
span. The XOR is put in column G and is used with the two conditions in the
same row. In other words, cell G5 contains an XOR that has two conditions —
a test to see if the percent change of Feb 2011 to Feb 2012 is an increase and a
test to see if the percent change of Feb 2012 to Feb 13 is also an increase. The
revenue percent change has been increasing — good news — and the XOR
returns the word FALSE. So, finally — when a manager looks over this report,
he can scan column G and if he or she sees the word FALSE, that ‘s a signal to
ignore. The question is, did revenue dip somewhere along the three years, in
February? The answer is no; that is, the answer is FALSE. The manager skips
looking any further at that line. The formula in cell G5 looks like this:
To the manger’s eye, other lines on the worksheet are worthy of attention.
For example, cell G15 contains an XOR that looks at the revenue change for
December over the three-year period. Sure enough, the revenue percent
change went up, then down — not good news. The XOR function returns TRUE.