Final Friday Fix: January 2024 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend.  On the Monday, we publish a solution.  If you think there is an alternative answer, feel free to email us.  We’ll feel free to ignore you.

The Challenge

As the sun blazes in its full glory and the beaches beckon, it's time to cool off with a refreshing Excel challenge.  While you sip on your lemonade or enjoy the gentle sea breeze, let's dive into a summer-themed Excel puzzle.

Imagine you're organizing a summer camp and you have a list of activities planned for each day of the week.  Each activity has a maximum number of participants, and you've got a dynamic list of campers signing up every day.  You need to ensure that the number of campers for each activity does not exceed its capacity.

Here's the twist.  You're given two Excel Tables: Activities_Table and SignUps_Table.  The Activities_Table lists various activities along with their maximum capacity.  The SignUps_Table records the name of the camper and their chosen activity for each day of the week.

Your challenge is to create a single dynamic Excel formula that will check the total number of sign-ups for each activity every day and flag any activity that exceeds its maximum capacity.

For example, if the activity 'Beach Volleyball' has a maximum capacity of 20 and 22 campers have signed up for it on Tuesday, the formula should flag 'Beach Volleyball' for Tuesday.

You can download the question file here which contains the following tables.  You can setup your solution however you like.

Remember, the solution should:

·         be dynamic and update immediately as new sign-ups are added

·         use only Excel formulas - no Power Query, Get & Transform, or VBA allowed

·         be case-insensitive.

Sounds like a breeze?   Gear up your Excel skills and let's make this summer camp a huge success! As always, we'll reveal one of the many possible solutions in Monday's blog.  Enjoy your weekend, and don't forget to stay sun-safe!