Bonus 4: Here are two ways to make finding the shortest route by hand faster 1. Deletion method (this is probably the faster of the two) We are going to delete large amounts of extra information and work with what remains. - Make a copy of the map - Determine what you think is the quickest route by visual inspection of the map. - Highlight big rectangular chunks of road that are not part of the quickest route - Clear all colors(and or text) in that rectangle - REPEAT: Highlight and clear. Highlight and clear. Highlight and clear. etc... - Do that until all pieces of road that are not part of the shortest route are gone. Don't worry if it looks pretty. - Count the remaining road cells that are the shortest route. 2. Find and Replace method We are going to use Find and Replace, but DO NOT do a Replace All - Make a copy of the map - Determine what you think is the quickest route by visual inspection of the map. - Open Find and Replace (CTRL-H) - Set your Find and Replace criteria for the road. NOTE: That you could use 2 methods here. Mark the shortest route with something unique *OR* Clear unwanted road sections that aren't part of the shortest route. - Click on Find Next to get the process started. - Now you will hold down ALT with one hand and keep your fingers above F(ind Next) and R(eplace) and select the appropriate action. *** Remember if you make a mistake you can always do a CTRL-Z to undo and replaces.
Well played as always, I think Bonus 1 is wrong, as you took the highest, and not the second highest Population. Also for Bonus 4 you could go iterative calculation for shortest distance. I also tried to do it manually but i missed one and didnt get to 158. Thanks for the video. Do you know when the results are going to be published?
Yeah, I saw that almost immediately after I hit submit! 🤦♂️ I also re-did B4 with formulas, but even after thinking about it I ended up feeling about the same as my initial instinct - the total time and the risk of a mistake are both pretty similar either way… Renier, that’s about as good a reason as any to put some things on hold. Enjoy!
Great Attempted Diarmuid! .... I will follow your naming convention for tables as T ... much quicker to reference the columns. Did you create a name range for the map?
I have to emphasize that good practice for zipping through esports rounds isn’t the same as good practice for working with things in the real world! 😂 Yes, I had a named range mp for the map area, and another one col for the color-coded version.
It’s not mine to give I’m afraid - but it should be available in the FMWC store shortly (the first round is already up). This is the section you’ll find it in: fmworldcup.com/product-category/case-studies/excel-esports-cases/
