Spreadsheet: 166576d1341648769-calculations-after-grouping-how-do-you-limit-calculations-to-a-single-group-testfile1
Sheet: Route10

Record | Time | X | Y | Raw | Difference off Mean | Group | Calculation1 | Calculation2 | Calculation3 | Calculation4
1 | 10:58:43.400000 | 369590.42 | 4564180.47 | 6.01 | "=ABS(E2-Average of Group1)" | "=INT((ROW()-ROW($G$2))/$B$114)+1" | "=SUM(ABS(E2-E3))" | "=(F2-$F$101)^2" | "=(F2-$F$101)^3" | "=(F2-$F$101)^4"
2 | 10:58:43.450000 | 369590.23 | 4564180.46 | 6.03
3 | 10:58:43.500000 | 369590.05 | 4564180.46 | 6.04 |  |  |  | These formulas are in quotes to show example calculations.
4 | 10:58:43.600000 | 369589.67 | 4564180.44 | 6.08
5 | 10:58:43.650000 | 369589.49 | 4564180.43 | 6.08
6 | 10:58:43.700000 | 369589.29 | 4564180.43 | 6.09
7 | 10:58:43.750000 | 369589.11 | 4564180.42 | 6.09
8 | 10:58:43.850000 | 369588.73 | 4564180.41 | 6.1
9 | 10:58:43.900000 | 369588.55 | 4564180.4 | 6.08 |  |  |  | What I am trying to do is to take very large data files, subdivide the data in into groups containing 10,20,50,100,500 samples (I want to see what the effect of varying the sample size is), run various calculations on those subsets of data, and classify the results into 10 classes.  I can subset/group the data and get it to do sums/average/etc. on those subgroups, but I need to calculate more than just the standard set of basic statistics.  I want to enter in other formulae and run those on the subgroups. This is an exercise in analyzing roughness across a surface, i.e., a profile.  While there are plenty of software companies that can give me all sorts of solutions for the entire profile, I need to look at subsets of the larger profile and classify local roughness... basically a subset window for calculations.  I have 146 profiles across this surface and would like to use those profiles to construct a 2D roughness classification.
10 | 10:58:43.950000 | 369588.36 | 4564180.39 | 6.08
11 | 10:58:44 | 369588.17 | 4564180.38 | 6.09
12 | 10:58:44.080000 | 369587.87 | 4564180.37 | 6.09
13 | 10:58:44.100000 | 369587.8 | 4564180.37 | 6.11
14 | 10:58:44.150000 | 369587.61 | 4564180.36 | 6.14
15 | 10:58:44.200000 | 369587.42 | 4564180.35 | 6.13
16 | 10:58:44.250000 | 369587.24 | 4564180.35 | 6.12
17 | 10:58:44.300000 | 369587.05 | 4564180.34 | 6.1
18 | 10:58:44.350000 | 369586.86 | 4564180.33 | 6.11
19 | 10:58:44.400000 | 369586.67 | 4564180.32 | 6.1
20 | 10:58:44.450000 | 369586.48 | 4564180.31 | 6.1
21 | 10:58:44.500000 | 369586.28 | 4564180.3 | 6.1 |  |  |  | I am trying to calculate standard roughness parameters: Ra = Average of the absolute deviation from the mean line (defined by the group size);  basically the arithmetical mean deviation: Rq = Root Mean Square Roughness, again dependent on the group size: Kurtosis = "spikiness"., etc.   So... I need to calculate the average "RAW" for each group, subtract the RAW of the individual sample from that average, and average all those values to provide a single average roughness for each group.  I need to repeat for 5 group sizes (10,20,50,100,500 samples per group) and for a number of various other calculations.  I have 146 sample sets to do.  All those results will be put back into a mapping program (in their proper spatial context) to define local roughness.  Does that make sense?
22 | 10:58:44.580000 | 369585.98 | 4564180.29 | 6.08
23 | 10:58:44.650000 | 369585.71 | 4564180.28 | 6.06
24 | 10:58:44.700000 | 369585.52 | 4564180.27 | 6.06
25 | 10:58:44.750000 | 369585.33 | 4564180.26 | 6.04
26 | 10:58:44.800000 | 369585.13 | 4564180.25 | 6
27 | 10:58:44.850000 | 369584.94 | 4564180.24 | 5.96
28 | 10:58:44.900000 | 369584.75 | 4564180.23 | 5.91
29 | 10:58:45.080000 | 369584.06 | 4564180.2 | 5.88
30 | 10:58:45.150000 | 369583.79 | 4564180.19 | 5.88
31 | 10:58:45.200000 | 369583.59 | 4564180.18 | 5.91
32 | 10:58:45.250000 | 369583.4 | 4564180.17 | 5.92
33 | 10:58:45.300000 | 369583.21 | 4564180.16 | 5.95
34 | 10:58:45.350000 | 369583.02 | 4564180.15 | 5.95
35 | 10:58:45.400000 | 369582.83 | 4564180.14 | 5.95
36 | 10:58:45.450000 | 369582.63 | 4564180.14 | 5.94
37 | 10:58:45.500000 | 369582.44 | 4564180.13 | 5.94
38 | 10:58:45.580000 | 369582.13 | 4564180.12 | 5.93
39 | 10:58:45.600000 | 369582.05 | 4564180.12 | 5.93
40 | 10:58:45.650000 | 369581.86 | 4564180.11 | 5.94
41 | 10:58:45.700000 | 369581.67 | 4564180.11 | 5.98
42 | 10:58:45.750000 | 369581.48 | 4564180.1 | 5.98
43 | 10:58:45.800000 | 369581.28 | 4564180.1 | 5.97
44 | 10:58:45.850000 | 369581.09 | 4564180.09 | 5.97
45 | 10:58:45.900000 | 369580.89 | 4564180.09 | 5.97
46 | 10:58:45.950000 | 369580.7 | 4564180.08 | 5.95
47 | 10:58:46 | 369580.51 | 4564180.07 | 5.95
48 | 10:58:46.080000 | 369580.2 | 4564180.06 | 5.93
49 | 10:58:46.150000 | 369579.93 | 4564180.06 | 5.91
50 | 10:58:46.200000 | 369579.74 | 4564180.05 | 5.92
51 | 10:58:46.250000 | 369579.54 | 4564180.04 | 5.91
52 | 10:58:48.010000 | 369573.16 | 4564180.13 | 5.91
53 | 10:58:48.090000 | 369572.88 | 4564180.14 | 5.97
54 | 10:58:48.160000 | 369572.64 | 4564180.15 | 5.97
55 | 10:58:48.210000 | 369572.46 | 4564180.16 | 5.96
56 | 10:58:48.260000 | 369572.29 | 4564180.16 | 5.96
57 | 10:58:48.310000 | 369572.11 | 4564180.17 | 5.96
58 | 10:58:48.360000 | 369571.94 | 4564180.18 | 5.96
59 | 10:58:48.410000 | 369571.77 | 4564180.19 | 5.96
60 | 10:58:48.460000 | 369571.59 | 4564180.2 | 5.96
61 | 10:58:48.510000 | 369571.42 | 4564180.21 | 5.97
62 | 10:58:48.590000 | 369571.15 | 4564180.23 | 5.97
63 | 10:58:48.660000 | 369570.91 | 4564180.25 | 5.98
64 | 10:58:48.710000 | 369570.73 | 4564180.26 | 5.92
65 | 10:58:48.760000 | 369570.56 | 4564180.28 | 5.97
66 | 10:58:48.810000 | 369570.39 | 4564180.29 | 5.96
67 | 10:58:48.860000 | 369570.22 | 4564180.3 | 5.95
68 | 10:58:48.910000 | 369570.05 | 4564180.31 | 5.97
69 | 10:58:48.960000 | 369569.88 | 4564180.33 | 5.96
70 | 10:58:49.010000 | 369569.71 | 4564180.34 | 5.94
71 | 10:58:49.090000 | 369569.43 | 4564180.36 | 5.92
72 | 10:58:49.160000 | 369569.19 | 4564180.38 | 5.94
73 | 10:58:49.210000 | 369569.02 | 4564180.39 | 5.95
74 | 10:58:49.260000 | 369568.85 | 4564180.4 | 5.94
75 | 10:58:49.310000 | 369568.68 | 4564180.41 | 5.95
76 | 10:58:49.360000 | 369568.51 | 4564180.43 | 5.95
77 | 10:58:49.410000 | 369568.33 | 4564180.44 | 5.96
78 | 10:58:49.460000 | 369568.16 | 4564180.45 | 5.95
79 | 10:58:49.510000 | 369567.98 | 4564180.47 | 5.96
80 | 10:58:49.590000 | 369567.7 | 4564180.49 | 5.97
81 | 10:58:49.660000 | 369567.46 | 4564180.5 | 5.99
82 | 10:58:49.710000 | 369567.28 | 4564180.52 | 5.98
83 | 10:58:49.760000 | 369567.11 | 4564180.53 | 5.99
84 | 10:58:49.810000 | 369566.93 | 4564180.54 | 5.99
85 | 10:58:49.860000 | 369566.76 | 4564180.56 | 5.99
86 | 10:58:49.910000 | 369566.58 | 4564180.57 | 5.94
87 | 10:58:49.960000 | 369566.4 | 4564180.58 | 5.93
88 | 10:58:50.010000 | 369566.23 | 4564180.6 | 5.94
89 | 10:58:50.090000 | 369565.95 | 4564180.62 | 6.01
90 | 10:58:50.160000 | 369565.7 | 4564180.64 | 6.02
91 | 10:58:50.210000 | 369565.53 | 4564180.65 | 6.03
92 | 10:58:50.260000 | 369565.35 | 4564180.66 | 6.02
93 | 10:58:50.310000 | 369565.18 | 4564180.68 | 6.01
94 | 10:58:50.360000 | 369565 | 4564180.69 | 5.99
95 | 10:58:50.410000 | 369564.82 | 4564180.7 | 5.96
96 | 10:58:50.460000 | 369564.64 | 4564180.72 | 6.02
97 | 10:58:50.510000 | 369564.45 | 4564180.73 | 6
98 | 10:58:50.590000 | 369564.16 | 4564180.76 | 5.96
99 | 10:58:50.660000 | 369563.9 | 4564180.78 | 5.95
100 | 10:58:50.710000 | 369563.72 | 4564180.79 | 5.96
Mean
[NAME]
Min
[NAME]
Roughness
[NAME]
Std_Dev2
DRatio
Skewness
Kurtosis
GroupSize | 20