Excel Expert Live Challenge with Bill Jelen: Reception Party Guests

/in /by

Mr. Excel, Bill Jelen, was gracious enough to be the first in a series that I call: Excel Expert Live Challenge. This is where I present a challenge to a Microsoft Excel expert and ask them to talk us through what they see, and the choices they make as they work toward a solution.

In this challenge Mr. Excel has to determine:
1. How many people are attending an event.
2. Calculate the size of available rooms
3. Determine which rooms are big enough–but not too big.

You’ll see: Text-to-Columns, INDIRECT, IFERROR, PRODUCT, Dynamic Arrays, UNIQUE, XLOOKUP, and Conditional Formatting.

0:00 Intro
1:28 Bill opens the challenge for the first time
3:40 Bill checks the data quality
4:00 Building a lookup table
7:33 Experimenting with INDIRECT
10:34 Adding a real world curve-ball
11:10 Troubleshooting
15:30 Discussion
18:40 Extra refinements
19:29 Conditional Formatting to highlight a row
20:30 Reflections and wrap-up

#ExcelChallenge #MrExcel #OzduSoleil

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book co-authored with Mr. Excel, Bill Jelen: Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2