Excel Shortest Route To 148 Locations Around The Supernatural World Using Excel - Episode 2637

Microsoft Excel Tutorial: Finding best routing for a trip with 148 locations.

Hey Excel fam! 🚀 Bill Jelen here with an epic Excel challenge. 🏋️‍♂️ Joined forces with the Supernatural workout community to optimize a 148-location quest using Excel magic. 🌍 Check out the journey, from Tahiti to Antarctica, and witness the distance-cutting madness unfold. 🌐

💡 First attempt started in Tahiti, but the route was a rollercoaster. 🔄 Tried a radical pivot, starting in Antarctica. 🏔️ Improved the distance to 90,253 miles, but still seeking perfection.

🤔 Experimented with K-Means clustering, exploring different strategies. 🧩 Finally settled on a plan starting in Alaska, sweeping through clusters and ending in Antarctica. 🌈 Achieved a 85,911-mile route – progress, but the quest for the ultimate efficiency continues.

👾 Calling all Supernatural enthusiasts! Add me, Bill Jelen, to your leaderboard. 🏆 If you're curious about Supernatural, hit me up for a 30-day free pass. 🎮 Shoutout to Roxanna, ABSN, and the community for the original quest. 🙌 Thanks for stopping by – until next time, Like, Subscribe, and Ring the Bell! 🔔💬 #ExcelQuest #SupernaturalChallenge #MrExcelMagic

The formula for distance between two coordinates on Earth: =ACOS(SIN(RADIANS(B2))*SIN(RADIANS(B3))+COS(RADIANS(B2))*COS(RADIANS(B3))*COS(RADIANS(C3)-RADIANS(C2)))*3958

Table of Contents
(0:00) MrExcel playing Supernatural VR to workout
(1:01) Shortest Route
(1:20) ABSN Quest to travel around the Supernatural world
(1:54) Arrange west to east by sorting Longitude
(2:22) Excel workbook to search by location
(2:33) Mapping the trip using Excel with animated trails
(3:24) Distance of trip is 177 thousand miles
(3:45) Macro to find closest point
(4:50) Running the macro repeatedly
(5:06) India, Scotland, Antarctica all left
(5:44) Down to 94K miles
(6:00) Choosing Antarctica as start point
(6:32) Too many disparate places left
(7:14) Do I remember Python?
(7:29) Using k-Means Clustering
(8:07) Running the macro on each of 6 clusters
(8:42) Nominally better 86K Miles
(9:02) Invitation to download workbook
(9:18) Supernatural VR
(9:33) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelchallenge #excelvba #excel3dmap #excelmaps

This video answers these common search terms:
excel supernatural workout quest, bill jelen excel challenge, optimize workout locations in excel, supernatural vr game excel tutorial, excel route optimization tutorial, 148 locations excel quest, k-means clustering for optimizing a route, excel macro for distance calculation, bill jelen 3d map excel tutorial, supernatural ninjas adventures excel help, absn adventures by supernatural ninjas, working out in supernatural vr in a meta quest headset, fitness game

The Macro in the Workbook:
Sub ProcessNext()
Dim WSF As Worksheet
Dim WSL As Worksheet
Set WSF = Worksheets("FindNext")
Set WSL = Worksheets("LogOfBest")

FinalRow = WSF.Range("A1048576").End(xlUp).Row
WSF.Range(Range("D5"), Range("D" & FinalRow)).FormulaR1C1 = "=ACOS(SIN(RADIANS(R2C[-2]))*SIN(RADIANS(RC[-2]))+COS(RADIANS(R2C[-2]))*COS(RADIANS(RC[-2]))*COS(RADIANS(RC[-1])-RADIANS(R2C[-1])))*3958"
WSF.Range(Range("D5"), Range("D" & FinalRow)).Value = WSF.Range(Range("D5"), Range("D" & FinalRow)).Value

WSF.Sort.SortFields.Clear
WSF.Sort.SortFields.Add2 Key:=Range( _
"D5:D" & FinalRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With WSF.Sort
.SetRange Range("A5:D" & FinalRow)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' Copy the closest to Log
NR = WSL.Cells(1048576, 1).End(xlUp).Row + 1
WSL.Cells(NR, 1).Resize(1, 3).Value = WSF.Range("A5:C5").Value

' Copy Row 5 to Row 2
WSF.Range("A2:C2").Value = WSF.Range("A5:C5").Value

' Delete row 5
WSF.Range("A5").EntireRow.Delete

End Sub

[excelisfun]

100 Thumbs Up : ) : ) All channels should end in is fun lol

[TonyDiem]

Good luck on your quest toward a healthier you! That's great!

[ricos]

I think the clustering approach is fairly solid. That gives you a good idea that you're in the right ballpark. I guess you could create a cluster within the cluster to refine further. Interesting challenge...

[davidjefferson]

My default tool in excel for finding an optimal solution is to use the solver add in and in this instance targeting a minimum. I think it would be possible use this, but doubt simplex Lp will work so you may not find the optimum solution, also may take time to compute.

[Geevs]

this is the traveling salesman problem, you can use Excel's data analysis solver tool after setting up a rather difficult but I am sure possible model.

[Excelambda]

Great challenge!!
This is the first draft
1 defined name entire array (places): pl
2 main functions MP (minimum distances for a chosen place, one of the rows) and MA (minimum distances for entire array, ascending order)
2 tool function FL for filtering, DS for distances

FL(e, a) excludes array "e" contained in array "a"



DS(v, p) extracts the min distance of any given row vector "v" from an array pf places "p"
=LAMBDA(v, p,
LET(
a, FL(v, p),
x, INDEX(v, 1, 2),
y, INDEX(v, 1, 3),
l, INDEX(a, , 2),
g, INDEX(a, , 3),
m, ACOS(SIN(RADIANS(x)) * SIN(RADIANS(l)) + COS(RADIANS(x)) * COS(RADIANS(l)) * COS(RADIANS(g) - RADIANS(y))) * 3958,
s, TAKE(HSTACK(SORTBY(a, m), SORT(m)), 1),
s
)
)

MP(k, [lst]) Minimum Consecutive Distances for a certain starting place "k", ; if "lst" =1 entire list, if "lst" omitted => {starting point, sum off all segments}
=LAMBDA(k, [lst],
LET(
r, IFNA(REDUCE(k, SEQUENCE(ROWS(pl) - 1), LAMBDA(v, i, VSTACK(v, DS(TAKE(v, -1), FL(v, ""),
IF(lst, r, HSTACK(INDEX(k, 1, 1), SUM(TAKE(r, , -1))))
)
)

MA() no variable Minimum of All places, iterates MP(k) for every member of "pl" (entire array) => total minimum distance in ascending order


If we define everything of the above we only have to call

=MA()

If you want I can make a post at the forum.
Note: This is the simplest approach, there are solutions with grafs that counts how many times a route is chosen as shortest between multiple places, more complicated but still simple, optimizing requires lot more iterations.