We all know the AND, OR & NOT formulas in Excel using which you can perform simple logical operations And, Or & Negate. But what if you are the chief of HR at ACME Company, where they have a strange rule on extra allowance like this:
Now, to calculate the dates in a month that meet this clause, we need an “exclusive OR” formula or what geeks call as “XOR” operation.
The logical operation … exclusive or (symbolized XOR, EOR), … results in a value of true if exactly one of the operands has a value of true. A simple way to state this is “one or the other but not both.”
Now, XOR or exclusive Or is a fairly common logical test, but there is no straight forward formula to test this. Instead we have to use a lengthy combination or AND, OR and NOT formulas to arrive at XOR.
For eg. assuming you want TRUE only when one of the two logical conditions A or B is TRUE,
you have to write,
=OR(AND(NOT(A),B),AND(A,NOT(B)))
[Afterall, that is how XOR operation is defined to begin with]
Now, that seems like an awful formula. May be there is a better formula after all?!? One that is less crazier than the HR clause of ACME Co.
Well, there is.
If you observe closely, XOR is nothing but <> (not equal to sign). So, instead of going nuts writing the lengthy ANDORNOT combination, you can simplify the formula to,
=A<>B
and it gives the same outcome.
So, the formula to find whether a given date (in cell A1) qualifies for bonus allowance,
=IF((WEEKDAY(A1)=6)<>(MOD(DAY(A1),5)=0),"Pay Bonus","Pay Regular")
More about logical formulas in Excel
AND Formula | OR Formula | NOT Formula | 51 common excel formulas
Do you XOR in real life?
There have been few occasions when I had to XOR in my worksheets. I found that writing the correct formula can be a bit tricky depending on how crazy the rule is. But almost always a combination of <>, NOT, AND and OR worked for me well.
What about you? Do you write formulas that involve complex IF clauses?
8 Responses to “Create a Combination Chart, Add Secondary Axis in Excel [15 Second Tutorial]”
[...] Select the “daily completed” column and add it to the burn down chart. Once added, change the chart type for this series to bar chart (read how you can combine 2 different chart types in one) [...]
[...] set the height series to be plotted on secondary axis. Learn more about combining 2 chart types and adding secondary axis in [...]
[...] Excel Combination Charts – What are they? [...]
[...] To show the years, I have used another dummy series and plotted it on secondary axis (related: how to add secondary axis?) [...]
Thanks for this one!
[...] Choisissez la colonne « Daily Completed » et ajoutez-la au graphique. Une fois ajoutée, changez le type de graphique pour cette série à histogramme (lisez comment combiner 2 types de graphiques en un : combine 2 different chart types in one) [...]
How do i create a chart that has negative numbers on axis x and y and plot them correctly? I cannot seem to understand how to do this, please help.
Thanks.
Nat
You can also plot 2 or more Y axes in Excel using EZplot or Multy_Y from Office Expander.com
There is a demo version to try.
Cheers.