ExcelHash 2019: Snakes & Regs – XOR, Dynamic Arrays, Linked Picture, Icon
Excel Hash is back for 2019. We almost
ended the year without doing it but we got it in. Me,
Leila Gharani, Bill Jelen, Mynda Treacy, John Acampora … we all have videos
that I’m gonna link to so that you can check. Them out. And Mike Girvin, ExcelIsFun, he wanted to do this but he wasn’t available for us. So, hopefully he’ll be
able to do it next time. The ingredients for excel hash 2019:
XOR, any dynamic array function, a linked picture and an icon. All
ingredients have to be used in an integrated solution. The ingredients have
to be critical to the solution and anything in Excel is fair game except
VBA. So, that means a person can use power query, power pivot, DAX, M-code … And you get to
vote. So, I’m gonna leave you a link so that you can vote to see who came up
with the best solutions. Here is my solution: Snakes & Regs! We have to
monitor people who are trying to get licensed to work with exotic snakes. In
order to be fully licensed a person has to do 30 hours with snakes. And they have
to complete 15 hours worth of work in regulations. So, over here we have a list
of what people have completed. Look at Stephan. He’s got a mix of snake
completions and regulations: 10 hours toward regulations. Now I took that data
into power query. All right let’s look at that briefly. There’s the source data and
then I pivoted it, and then sorted the rows. Simple. Now here is where I used
XOR. Because we have to figure out who has completed their hours in one
category or the other not both. If they’ve completed the hours in both, then
a whole separate team takes care of that. That’s
where they start processing a license and having a ceremony and everything. BUT! People get $500 when they’ve completed one category or the other. That’s
where’s XOR came in. The formula here is slick. I use XOR to find out if a person
has met either the snakes requirements are the regulations requirements–not
both. And that gives a true or false. Then I use the double-unary to turn the trues into
1and falses into zero. And then multiply that times the bonus of $500 let me show you how that’s done. Equals XOR. Open parentheses. And let’s use Bailey as an example. IF this cell is greater than or equal to
the snakes requirements, comma, this cell is greater than or equal to the 15, close
parentheses. Okay it’s true because one criteria is true and the other one is
false. Next the double unary, double minus sign,
equals, and then multiply that times the bonus. Go here times; the bonus; and I’m
gonna use F4 to lock that down. Okay. That’s how we get to $500 XOR, double-unary.
Next I used the dynamic array FILTER function in order to get down to the
people who have earned a bonus. And here is where that is. So, we are filtering
this table where the complete column is greater than zero. Now how did I
integrate the icon? Okay let’s go over here to graphics. This is sexy! We are
also responsible for the progress of these four people Hortencia, Mathéo,
Océane and Stefan.
Not anybody else. Now these are progress bars that are
showing us say for example Hortencia is at 43% of her snake hours and 93% of her
regulatory hours. And I thank Fred Le Guen for this. You got to check out
his video on making a progress bar with icons, and I’ll quickly show you whether
icons are. They are here on the insert tab. And then icons. And we’ve got a lot
of them. And there is the snake. Now let’s go over to our people. On the left side
we’ve got everybody that we’re concerned with and they’re all stacked up. And then
on the right side, we’ve got the same data but it’s split. Snake hours up top,
regulatory hours on the bottom. And these are linked pictures. Let me show you how
that’s done. Go back to graphics. I’m gonna go here
and then slide over right-click, copy then our people and then right-click paste as a linked picture. Now with this
this way, I can move it wherever I want to. I can resize this. I can crop it. I can
tilt it. And it’s still a live picture connected to whatever is on the graphic
sheet. So let me show you what’s cool about this. Go to the graphics page. This
hot-pink is kind of loud so I’m gonna go to conditional formatting. Manage rules. this worksheet.
Highlight the rule. Edit the rule. Let’s make it this blue. Okay. Okay.
those changed. Let’s go back to our people. That did not change. We go to data.
Refresh. That’s what we get from linked pictureds linked pictures. One more thing and then we’re gonna get out of here!
Progress & bonuses. Slide over. We’re gonna add some more data. Remember Mathéo had no hours with snakes.
Let’s look at that. Right. 17 hours in regulations. And we’re adding Tessa for
six hours of regulations. Okay let’s go up here. Refresh.
BOOM. Look! There’s Mathéo’s six hours.
Tessa is now due a bonus. Go to Our People. And look at that. Mathéo is 20%
complete. Excel Hash 2019. There it is! Go check out the other videos and please
vote. Hopefully you liked my video. All right.
See you next time.