ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
A tool for calculating Super Mario Sunshine Any% World Splits. Make a copy of me to edit!Adapting to Different Routes
2
This sheet can be used to aggregate any set of contiguous segments, for example for savewarp routes or to toggle Airstrip inclusion. To change the route:
3
Usage
4
1.) Export your splits into an Excel file in LiveSplit (from the Share menu).1.) Delete any columns in the "Input" tab that won't be used.
5
2.) Import this file into Google Sheets via File > Import > Upload, selecting "Create New Spreadsheet".2.) Edit each of the cells B3 to J3 in the "Calc" tab individually. They look like this:
6
3.) Open that new sheet's "Segment History (Real Time)" tab and press Ctrl+A to select it, Ctrl+C to copy.=if(countblank(Input!A3:F3)=0,sum(Input!B3:F3),"")
7
4.) On this sheet (that you're reading), go to Data > Named Ranges, select InputBody, then wait for some cells to get highlighted. Now, right-click those cells and select Clear Rows.
a.) Replace the right range (B3:F3) with the cells from "Input" tab you want to include for that world.
8
 b.) Set the left range (A3:F3) to be the right range with an extra column on its left included.
9
5.) On the "Input" tab, select cell A2, then press Ctrl+Shift+V to paste the other sheet's values.3.) Select all of row 3, then press Ctrl+C to copy the updated row. Go to Data > Named Ranges, select CalcBody, then press Ctrl+V to paste it over the whole sheet.
10
6.) Check that the "Calc" tab has at least as many rows as "Input" (if not, see Expanding Sheet Capacity).
11
 Results appear in the top row of the "Calc" tab.
12
2.5) [optional]: This process rounds times to 2dp. To preserve 3dp, in the same tab specified in step 3, press Ctrl+A enough times to select the whole sheet, then Ctrl+Click the first column to remove it from the selection. Now, go to Format > Number > More Formats > Custom Number Format, and change it to [hh]:mm:ss.000, then click Apply. Proceed to step 3.The idea in this formula is to sum all the level segments comprising a world segment. But we need to ignore bad data from missed splits. Hence, there's a non-blank requirement for every segment in the world, as well as the level right before it. This ensures that the given run split on every boundary between segments that's included in the sum, and so every included segment is accurate.
13
14
Expanding Sheet Capacity
15
1.) At the bottom of "Calc" tab, use "Add ... more rows at bottom" to add enough to cover "Input" tab.
16
2.) Go to Data > Named Ranges, then edit both CalcBody and InputBody so that the number on the right equals the biggest row number on the respective sheet.
17
3.) In the "Calc" tab, select all of row 3, then press Ctrl+C to copy. Then, go to Data > Named Ranges, select CalcBody, and press Ctrl+V. This fills out the formulae on "Calc".
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100