MIC'D UP: EXCEL DEVELOPER TAKES ON THE EXCEL WORLD CUP
By popular demand, this video is about my experience of the Excel World Cup. In this video, I show you the question I answered with a live recording of how I answered it. We break it down into four steps and I explain my thinking AND the techniques used in each step.
Nearly 300 people, including me, entered this round of the Excel World Cup, so where would I rank?
Would I even submit the file in time? Will you spot the critical formula-building mistake I made ..?
Will you be able to do it yourself? I can't share the actual materials, but I have mocked up a similar file for you to have a go!
EXCEL DOWNLOAD FILE WITH CHRIS' SOLUTION:
WATCH THE VIDEO Here
Once you solve the question, and answer key displays in the file - copy the answer key into the YouTube comments to show you completed the question ...
The question is written by Brian Wright and is about THE FORMULA BAR AND GRILL. It's a typical restaurant where you can order burgers, salads and the like - a menu is included. The menu items each have two letter codes (bacon burger is BB) and fall into three categories - entree, side and drink.
An order is a text string of two-letter item codes. Our job is to analyse the orders. This is question 3 of 7 and is rated ... medium!
Specifically, we must establish when a 'combo meal' applies - when an entree, side and drink appear on the same order.If so, a discount applies, and multiple combo meal discounts can apply to the same order. So, how much is the order total, including the combo meal discount, or discounts?
I structured my answer into four steps.
You can use these steps to do the question yourself, and make sure you copy the answer key to the comments!
00:00 THE EXCEL WORLD CUP
01:34 STEP 1
The first step is to pull through order text strings for the order numbers specified in the question.
This calls on the historical orders dataset on the Historical tab which shows order numbers and the 'order ticket'. It's a basic VLOOKUP of the order number in the historical dataset (dataset 1). Though I use OFFSET / MATCH here.
02:38 STEP 2
Step 2 is to 'parse out' the orders into individual food item codes. This seems simple enough using the MID formula - but can you spot the HUGE mistake I made at this stage?
04:01 STEP 3
With the item codes in individual cells, we can now 'look up' each item and get its category.
Note a menu is provided showing the item code, category and price. We have the item codes, so we can look up the category to help us work out if a combo meal applies.
I try this with VLOOKUP - but what catastrophic error do I make at this point?
05:53 STEP 4
The final step is to get the number of combo meals using COUNTIF and MIN. Then, we can look up prices and calculate overall order value but get into a huge mess here. Will I have time to complete the question and submit the file?
So, I didn't manage to complete this question. Can you? Download the file and complete my version of the question. Then, put the answer key in the YouTube comments ...
But, I did manage to complete the two previous questions, scoring 200 points. Where did that put me in the Excel World Cup rankings? Find out at the end of the video.
The techniques I used in this video are just a part of my Excel toolkit. To see the breakdown of my whole Excel toolkit, click the link below.
FOLLOW-UP VIDEOS
MY EXCEL TOOLKIT (FULL BREAKDOWN)
DO WANT VLOOKUP CAN'T DO WITH OFFSET / MATCH