I’m dedicating this post to the first person to compliment on the Excel Workbook blog…
Some time has passed since Ron‘s Beijing Olympics post. It’s now 2020 and Laser Tag has become an official Olympics sport. The globally famed Laser Tag champion Wang Y. blew her way through the games with amazingly acrobatic shooting, docking, and sniping skills. After the thrilling final round where all athletes gave their 101%, you as the game admin recorded the scores as follows:
Having studied Ron‘s medal comparison model for the past three Olympics, you now have full appreciation for the neat user interfaces that Excel allows you to create. Now you want to use that knowledge to create an entirely new and personalized score reporting system. Here’s what you have in mind:
- Scores will be presented in a bar graph
- Each participating athlete will receive a personalized score card
- Each athlete will be listed first on her score card with name spelled out on the graph, in honor of her excellent sportsmanship
- All the other athletes will be listed in alphabetic order by country code; their names will not be shown
For example, the score card for China’s Wang Y. should look like this – her name, country, and score are listed on top, followed by the other countries in order: Australia, Brazil, (skipping China), Germany, and so on.
While this task could be done fairly quickly by hand, it could get very tedious. You are a busy game admin after all, and don’t have the time to personalize 10 score cards manually. You already know how to locate the chosen athlete, but how do you re-order the remaining list?
The INDEX() function comes in handy. It returns the value of an element in a table or an array, selected by the row and column number indexes. It often complements the MATCH() function, and can be considered as an inverse function to MATCH(). Note how it’s accomplishing the same task as the OFFSET() function used for Beijing Olympics. Now you write:
CurrentItemPosition – (CurrentItemPosition<=ChosenItemPosition)*1
The second argument determines whether to shift the reference by one row. When China is selected (position 3), the 1st and 2nd countries need to be shfited into positions 2 and 3, but all other countries in the list will stay put for positions > 3.
Now you have a neat user interface that generates a unique score card as easily as a drop-down menu selection. Many people resort to writing macros for this type of tasks, but they can more often than not be done with a relatively simple set of formulas.
The following is the score card for the USA athlete, who won silver.
Congratulations to all!