Microsoft Excel Tutorial: Brute Force Solving in Excel

In Episode 2180 of the MrExcel Podcast, we tackle a challenging scheduling problem sent in by L, the Commissioner of the Swim League. This year, the Swim League has eight teams, and the goal is to arrange the schedule so that each team swims against every other team twice. It's no easy feat, and even L's previous method of pressing F9 until close isn't working with eight teams.

Bill Jelen, aka MrExcel, provides a unique solution to this conundrum. He explains how to evaluate and measure the closeness of each schedule by using Excel functions. The goal is to find a "Measure of Closeness" that indicates how close the schedule is to the desired outcome. Bill also discusses how to minimize the number of times a team plays another team four or more times.

Throughout the video, Bill demonstrates the power of Excel's F9 key and how you can keep pressing it to get closer to the ideal schedule. He even shares a custom macro that automates the process, enabling Excel to press F9 thousands of times to find the best solution.

This episode is a deep dive into Excel problem-solving, and Bill's approach to handling complex scheduling challenges is a testament to the versatility of Excel as a tool. Check out the video for a fascinating exploration of Excel's capabilities in solving real-world problems.

Subscribe to the MrExcel YouTube channel and click "Like" to support more insightful content and algorithmic problem-solving.

#MrExcel #ExcelScheduling #SwimLeague #F9Key #AlgorithmicProblemSolving #ExcelMagic

#excel
#microsoftexcel
#exceltricks
#evergreen
#simulation
#montecarlo
#excelvba
#recoverexcel

This video answers these common search terms:
can excel do monte carlo simulation
how to do a monte carlo simulation in excel
how to perform monte carlo simulation excel
how to program a montecarlo simulation in excel
how to run a monte carlo simulation in excel
how to run monte carlo simulation in excel
how to set up monte carlo simulation in excel
how to use monte carlo simulation in excel
how to do a simulation in excel
how to do simulation in excel
what is simulation in excel

Table of Contents
(00:00) Excel Recalc Until Best or Brute-Force Solving
(00:21) Swim League Scheduling for 8 teams
(00:43) Meet each team twice?
(00:58) My answer: Press F9 faster.
(01:11) Measure of Closeness?
(01:27) 8 home teams with 3 or 4 other teams
(01:46) How many ways to arrange the competitors?
(02:02) 35 different ways
(02:34) Random Number between 1 and 35
(02:57) Not really Team 9
(03:14) Press F9 for different schedule.
(03:25) Single measure: is schedule good?
(03:39) Counting match ups
(04:21) Arrange so smaller number first
(04:46) Use COUNTIF to see who meets
(05:03) Press F9 until close
(05:41) Count team plays same team 3 times
(06:11) Get close to 28
(06:29) Count permutations
(06:53) Can't loop 2.2 trillion permutations
(07:08) Press F9 to see range is 21 to 26
(07:29) When close, copy the schedule as temporary best.
(07:46) Use Macro to Calc once and see if better
(08:41) Macro to press F9 over 7000 times
(09:00) Found goal of 28 matchups
(09:33) 42K trials out of 2.2 trillion possibilities
(10:04) Disable Screen Updating
(10:28) Move cell pointer after 1000 trials
(10:47) Finally - a schedule that meets all criteria after 3 million trials
(11:36) If two pools have only 4 swim lanes
(12:02) One ten-thousandth of possibilities
(12:53) Look at the macro
(13:45) VBA Book
(14:00) Wrap-up
(14:19) Clicking Like really helps the algorithm


Download Excel file from [ Ссылка ]
Press F9 Until Close
Guess Until Correct
Brute Force Solving
Measure of Closeness
L: I am the commissioner of a swim league
There are eight teams this year. Each team hosts one meet and is the home team.
A meet will have 4 or 5 teams. (5 because you need some meets to have 5 to solve the problem. 4 because some pools only have 4 lanes.)
How to arrange the schedule so every team swims against every other team twice?
In the past, when we had 5, 6, or 7 teams, I could solve it by pressing F9 until I was close.
But this year, with 8 teams, it is not coming out.
Need to press F9 many times
Customize your model so it results in a one-number "Measure of Closeness"
Keep you eye on that number and press F9 until you get a better answer
Save that answer
Keep pressing F9 until you get close enough
Use a VBA macro to automate that process

Join the MrExcel Message Board discussion about this video at [ Ссылка ]

свернуть