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:
- Icons
- 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.
Video Explanation
The video below is a quick overview of the features used to build the solution
Download Workbook
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:
It then extracts them to the ‘Teams in Round 3’ table in columns J & K using the dynamic array FILTER function and linked pictures which pick up the team icon from column C.
Components
- 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
You can watch each video in this playlist, then go here to vote for the entry you think built the best and most integrated solution.
Excel XOR Function
Peter Bartholomew
Curious as to the rules of this game; how does one get 8 wins from 7 matches?
As for the competition you are in, Oz has handed you a dud ingredient! XOR is not compatible with array formulas. It is rather like insisting you use curry powder in your strawberry trifle; I would tell him what he can do with that ingredient. The best use I could come up with is
= IF( XOR( IWantToUseArrayFormulas?, IWantToUseXOR? ), “Great!”, “Rubbish!” )
which keeps it from wrecking the dish.
The formula I used in place of XOR was
= MOD(Match1+Match2, 2)
which gives an array of results, one for each team.
Mynda Treacy
Hi Peter,
Those lucky Unicorns had a bye in the first round which was counted as a win and Tigers had the bye in the second round ;-P
It’s not Oz’s fault…the 4 ingredients were selected randomly from a list of topics suggested by the general public in the lead up to last year’s competition. I agree it’s not the best function and can’t see I’ll ever use it again. I like your MOD solution.
Mynda
jim
neatly done Mynda!
Mynda Treacy
Thanks, Jim!
Mike cronin
Mynda, looks good just would have spelled “Wales” as “Whales” .
Wales is a country in the UK
Mynda Treacy
🙂 Thanks, Mike! Must have had the UK on my mind!
jim
and there’s also New South Whales in Oz
eba
Greeeeeeat!
Mynda Treacy
😀 thanks, Eba!