Excel Hash is an Excel competition dreamed up by fellow Excel MVP, Oz du Soleil. It’s based on the popular cooking show, Chopped. The one where chefs are given a mystery box of four ingredients to make a meal from. 2018 was the inaugural year for Excel Hash and I was honoured to win!
It’s back for 2019 and the four mystery ingredients we’ve been given are:
- Dynamic Array Functions*
- The XOR Function
- Linked Picture(s)
*Dynamic Array functions are available in Office 365 only and are being rolled out to users over the coming months.
Our task was to come up with an Excel tool that used all four ingredients in an integrated way. We were free to choose the data and could use other tools from the Excel pantry except VBA!
You get to rate my entry here based on how well you think it integrates the four ingredients into the solution. There is a link below to the playlist containing all entries, but first...you can see my entry in action in the animated image below:
Note: If the image below isn’t animated, click here to see it on the blog.
The video below is a quick overview of the features used to build the solution
Enter your email address below to download the sample workbook.
Dynamic Playoffs Table
My entry is a competition table that identifies which teams need to play a third match from a list of teams that have already played two matches.
- Teams that won both matches automatically qualify for the semi-final – Sharks and Tigers are an example of that
- Teams that lost both matches are out – that’s Roosters
- Teams that only won one match play a third to qualify for the semi-final – that’s Whales, Snakes, Lions and Unicorns
Based on these rules I’ve used the XOR function to determine which teams require a third match, which you can see the results of in column G below:
- Icons have been inserted in column C for each team’s logo
- The XOR formula in column G returns TRUE where a team won one match and lost one match. Teams that won both or lost both return FALSE. This is inline with the competition rules above.
- The dynamic array FILTER formula in column H returns the list of teams where XOR returned TRUE.
- Dynamic named ranges lookup the team name returned by FILTER in column J and return the cell containing that team’s logo. There is a dynamic named range for 4 teams, as you can see in the name manager below:
- Linked Pictures were copied from the logo cells in column B and pasted into column I.
IMPORTANT: The cell containing the picture (image object) must be as big or bigger than the picture, as it’s the cell you’re copying, not the image object.
The link references for each picture were then replaced with the dynamic named ranges for each team:
Competitors and Voting
My fellow Excel MVP competitors are:
- Bill Jelen - MrExcel
- Leila Gharani
- Jon Acampora - Excel Campus
- Oz du Soleil - Excel on Fire