This is my entry in the Excel 2019 Excel Hash Competition. Vote for your favorite video here: Microsoft Forms
Watch the other entries here: Excel Hash 2019
Covered in this video: Why does the XOR function return "wrong" results with more than two inputs.
Comparing XOR to ISODD
How to use icons in Excel
Linked Picture with a conditional selection of which icon to use.
Watch the other entries here: Excel Hash 2019
Covered in this video: Why does the XOR function return "wrong" results with more than two inputs.
Comparing XOR to ISODD
How to use icons in Excel
Linked Picture with a conditional selection of which icon to use.
Transcript of the video:
Learn Excel from MrExcel. Podcast Episode 2303 XOR is Odd.
Hey, welcome back to Mr Excel Netcast.
I'm Bill Jelen Today we're talking about the XOR function introduced in Excel 2013. It's an exclusive or.
Now we've always had the AND function and the OR the OR function.
But eXclusive OR is different, right?
So if both items are false, then of course both XOR and OR return False. First one false, second one True is True.
First one True, second one False. That's True as well.
But here's the difference.
Exclusive OR says hey, we're looking for exactly 1 item to be correct. for it to be True.
If they both are True then eXclusive OR says False.
I was excited when this came out - because I'm working on a scheduling committee.
First thing I have to do is find all of the shifts where exactly one person asked for that shift.
Then you don't have to use any seniority rules. Bam, you got the shift. But check it out.
Does it work right?
So here we have a whole bunch of people who are volunteering for this organization.
If one of them says yes, I'll take it, XOR works. Two people?. That should make eXclusive OR say no deal.
But here's the problem.
If I go to a third one.
Exclusive OR incorrectly reports this as True. Is there exactly 1 item? Exclusive OR says: Yes.
It's not! It's wrong! I remember complaining about this on Twitter. I was very quickly rebuked by electrical engineers.
I said Hey, this isn't working right and they said don't complain to Microsoft about this.
Because it's working exactly like the electrical engineers want it to work.
We need it work the same way that the computer chip works and so here I have a 10 different cases with varying numbers of Trues, and False values.
And if you have exactly one true then XOR returns true, but if there's two, it's false.
Now see if you can figure out what the pattern is. 3 it says True there's exactly 1. Four? No. Five? Yes.
Six? No. Seven? Yes. What the heck is happening here?
How is XOR saying that we have exactly 1 item.
Well, the chip the computer chip.
It only accepts two inputs, so this has to be evaluated.
These ten arguments are these ten arguments have to be evaluatedbut two at a time. So you take the first 2.
Is exactly one of those true? Yes, it is. Then take this answer.
The first answer, the sub answer and the next item is exactly one of those true? Yes, it is.
Then this answer, and that is exactly one of those true?
No, we have two that are true, so it's false right?
And you keep going out here and you eventually end up with this, which to me is a wrong answer, that it is True and it doesn't matter what order these appear in here I can calculate.
Now I'm using the RANDARRAY. I am sorting by the RANDARRAY.
Two dynamic array functions there. Those are brand new.
No matter what order you evaluate this in.
If there's three items, it's going to end up True, and what it simply is really doing is just counting if there's an ** odd ** number of items.
And hey, we don't need eXclusive OR to do that.
We've had ISODD to do that.
Going back to at least Excel 2007 and the Analysis ToolPak even before that.
Why couldn't electrical engineers just used this instead of needing eXclusive OR?
Now if you agree with me, what we need is a way to count exactly 1 item is True, then use this formula here equal COUNTIF if they're all false then it's false, but there is exactly one true.
Then it returns True, but if there's ten Trues or three Trues or any number of Trues... Here we have seven Trues.
It comes out as False, so it works much better than eXclusive OR.
Now Hey, this video is part of the 2019 Excel Hash competition where we had to use these items.
You'll see your other favorite Excel creators on YouTube creating this.
Please take just 30 seconds down the YouTube description. There's a link there.
Go vote for this video. Hey, I want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
Hey, welcome back to the out take. Did you see it? The icon there?
Also the two icons that appeared here depending on whether I put in an even number or an odd number.
This is the behind the scenes. How did you create that? How to use those icons.
Icons came along in about 2018.
If you have Office 365, they are under Illustrations. They've actually been adding to them.
It's pretty cool and I came out here looking for something that kind of indicated on or off to me and there really wasn't anything that I like for that.
So I ended up using the battery being completely full or half full.
It is your choice and what's nice here is you can choose two different icons, so this icon and that icon and insert those into Excel.
They show up like that. They are completely re- sizable, which is cool.
You can change the colors. There's two colors.
There's a fill color.
So like that and also an outline color, so you might have to change them both. You can barely see it there.
Let's make it larger so you can see how those vary like that.
So fill color and an outline color.
Now how did I make this work as a conditional linked picture and hey - a big shout-out here to former Excel MVP Charley Kyd.
He figured this awesome little trick out here.
So what I'm going to do is I'm going to two cells, one on top of the other.
Put the On and Off icons there and then I choose that cell, copy it with Ctrl+C and then come back to where I want the item to appear like here and say Home, Paste, Linked Picture.
So that is a picture of whatever is happening in this other cell.
What Charlie realized is that when this is selected, holy smokes, that's a formula.
So if I change that formula, if I change it from S39 to S40, the picture itself is going to change, right?
And Charlie used that to to his advantage.
So right here I'm going to have a number, either zero or 1 that are going to appear there, and I'm going to use the OFFSET function.
And instead of pointing to this cell S39, I'm going to start. Well.
I guess I can start from S39 and go down either zero or one rows.
Now if I try and enter the OFFSET function here, it's not going to work, so Formulas tab Define Name, I'll call it.
CoolCharlie.
And it's going to be equal OFFSET( This sheet is called SeemsWrong.
And we're going to start in S39, right?
Yeah right there. So S39. SeemsWrong, Dollar sign, S, Dollar sign 39.
We're going to start there and how many rows down? We go that's going to be the answer from right here.
This number that is there is currently zero.
How many columns over? Zero columns over. How tall? One cell tall.
One cell wide.
We now have something called Cool Charlie.
And that defined name is allowed to be used as the reference for that link picture so right now instead of S40 I'm gonna say equal CoolCharlie.
And as this zero changes to a one, the picture changes of course it doesn't have to be just two.
You could have 10 or 20 or 30 of these icons. It is a really awesome trick.
Again shout-out to Charley Hyd, former Excel MVP, who came up with that trick.
How about dynamic arrays?
With dynamic arrays were used in this video, all right, the first one right here.
I'm using the SEQUENCE function to generate the numbers one through 10, and if those are less than or equal to this number over here in column D. Then give me a True, otherwise give me a False.
So what that gave me is the exact number of true.
So here where this is 5, I'm getting 1 2 3 4 5.
D12# says this one formula is going to return all twelve of those.
That's a single formula returning that awesome array there for me.
The other thing I wanted to show that it doesn't matter if it's True/True or True then a whole bunch of False is True, so I had to generate a random sort.
So I use the SORTBY function and then The RANDARRAY is another. So I have 10 items I want to sort.
I want to sort them randomly every time that I press calculate.
Now that number of Falses stays the same, but the sequence of them completely changes.
Cool, cool, I've used SORTBY to randomly sort something. It is great for employee drug testing.
Where you choose two each quarter or something like that.
Hey, welcome back to Mr Excel Netcast.
I'm Bill Jelen Today we're talking about the XOR function introduced in Excel 2013. It's an exclusive or.
Now we've always had the AND function and the OR the OR function.
But eXclusive OR is different, right?
So if both items are false, then of course both XOR and OR return False. First one false, second one True is True.
First one True, second one False. That's True as well.
But here's the difference.
Exclusive OR says hey, we're looking for exactly 1 item to be correct. for it to be True.
If they both are True then eXclusive OR says False.
I was excited when this came out - because I'm working on a scheduling committee.
First thing I have to do is find all of the shifts where exactly one person asked for that shift.
Then you don't have to use any seniority rules. Bam, you got the shift. But check it out.
Does it work right?
So here we have a whole bunch of people who are volunteering for this organization.
If one of them says yes, I'll take it, XOR works. Two people?. That should make eXclusive OR say no deal.
But here's the problem.
If I go to a third one.
Exclusive OR incorrectly reports this as True. Is there exactly 1 item? Exclusive OR says: Yes.
It's not! It's wrong! I remember complaining about this on Twitter. I was very quickly rebuked by electrical engineers.
I said Hey, this isn't working right and they said don't complain to Microsoft about this.
Because it's working exactly like the electrical engineers want it to work.
We need it work the same way that the computer chip works and so here I have a 10 different cases with varying numbers of Trues, and False values.
And if you have exactly one true then XOR returns true, but if there's two, it's false.
Now see if you can figure out what the pattern is. 3 it says True there's exactly 1. Four? No. Five? Yes.
Six? No. Seven? Yes. What the heck is happening here?
How is XOR saying that we have exactly 1 item.
Well, the chip the computer chip.
It only accepts two inputs, so this has to be evaluated.
These ten arguments are these ten arguments have to be evaluatedbut two at a time. So you take the first 2.
Is exactly one of those true? Yes, it is. Then take this answer.
The first answer, the sub answer and the next item is exactly one of those true? Yes, it is.
Then this answer, and that is exactly one of those true?
No, we have two that are true, so it's false right?
And you keep going out here and you eventually end up with this, which to me is a wrong answer, that it is True and it doesn't matter what order these appear in here I can calculate.
Now I'm using the RANDARRAY. I am sorting by the RANDARRAY.
Two dynamic array functions there. Those are brand new.
No matter what order you evaluate this in.
If there's three items, it's going to end up True, and what it simply is really doing is just counting if there's an ** odd ** number of items.
And hey, we don't need eXclusive OR to do that.
We've had ISODD to do that.
Going back to at least Excel 2007 and the Analysis ToolPak even before that.
Why couldn't electrical engineers just used this instead of needing eXclusive OR?
Now if you agree with me, what we need is a way to count exactly 1 item is True, then use this formula here equal COUNTIF if they're all false then it's false, but there is exactly one true.
Then it returns True, but if there's ten Trues or three Trues or any number of Trues... Here we have seven Trues.
It comes out as False, so it works much better than eXclusive OR.
Now Hey, this video is part of the 2019 Excel Hash competition where we had to use these items.
You'll see your other favorite Excel creators on YouTube creating this.
Please take just 30 seconds down the YouTube description. There's a link there.
Go vote for this video. Hey, I want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
Hey, welcome back to the out take. Did you see it? The icon there?
Also the two icons that appeared here depending on whether I put in an even number or an odd number.
This is the behind the scenes. How did you create that? How to use those icons.
Icons came along in about 2018.
If you have Office 365, they are under Illustrations. They've actually been adding to them.
It's pretty cool and I came out here looking for something that kind of indicated on or off to me and there really wasn't anything that I like for that.
So I ended up using the battery being completely full or half full.
It is your choice and what's nice here is you can choose two different icons, so this icon and that icon and insert those into Excel.
They show up like that. They are completely re- sizable, which is cool.
You can change the colors. There's two colors.
There's a fill color.
So like that and also an outline color, so you might have to change them both. You can barely see it there.
Let's make it larger so you can see how those vary like that.
So fill color and an outline color.
Now how did I make this work as a conditional linked picture and hey - a big shout-out here to former Excel MVP Charley Kyd.
He figured this awesome little trick out here.
So what I'm going to do is I'm going to two cells, one on top of the other.
Put the On and Off icons there and then I choose that cell, copy it with Ctrl+C and then come back to where I want the item to appear like here and say Home, Paste, Linked Picture.
So that is a picture of whatever is happening in this other cell.
What Charlie realized is that when this is selected, holy smokes, that's a formula.
So if I change that formula, if I change it from S39 to S40, the picture itself is going to change, right?
And Charlie used that to to his advantage.
So right here I'm going to have a number, either zero or 1 that are going to appear there, and I'm going to use the OFFSET function.
And instead of pointing to this cell S39, I'm going to start. Well.
I guess I can start from S39 and go down either zero or one rows.
Now if I try and enter the OFFSET function here, it's not going to work, so Formulas tab Define Name, I'll call it.
CoolCharlie.
And it's going to be equal OFFSET( This sheet is called SeemsWrong.
And we're going to start in S39, right?
Yeah right there. So S39. SeemsWrong, Dollar sign, S, Dollar sign 39.
We're going to start there and how many rows down? We go that's going to be the answer from right here.
This number that is there is currently zero.
How many columns over? Zero columns over. How tall? One cell tall.
One cell wide.
We now have something called Cool Charlie.
And that defined name is allowed to be used as the reference for that link picture so right now instead of S40 I'm gonna say equal CoolCharlie.
And as this zero changes to a one, the picture changes of course it doesn't have to be just two.
You could have 10 or 20 or 30 of these icons. It is a really awesome trick.
Again shout-out to Charley Hyd, former Excel MVP, who came up with that trick.
How about dynamic arrays?
With dynamic arrays were used in this video, all right, the first one right here.
I'm using the SEQUENCE function to generate the numbers one through 10, and if those are less than or equal to this number over here in column D. Then give me a True, otherwise give me a False.
So what that gave me is the exact number of true.
So here where this is 5, I'm getting 1 2 3 4 5.
D12# says this one formula is going to return all twelve of those.
That's a single formula returning that awesome array there for me.
The other thing I wanted to show that it doesn't matter if it's True/True or True then a whole bunch of False is True, so I had to generate a random sort.
So I use the SORTBY function and then The RANDARRAY is another. So I have 10 items I want to sort.
I want to sort them randomly every time that I press calculate.
Now that number of Falses stays the same, but the sequence of them completely changes.
Cool, cool, I've used SORTBY to randomly sort something. It is great for employee drug testing.
Where you choose two each quarter or something like that.
