I have a list: multi column and multi rows in an excel sheet. The first column is TIME in seconds with a unique number on each line. The last column is called STEP (as a number from zero and up). Each STEP has a different number of rows (time at a data point which varies) that corresponds to a STEP. The Columns in between are my data (let’s call them weight, depth, height, distance). I can readily pull an average for each data series (weight, depth, height, distance) for each full STEP (I just use a pivot table). However, there are instances where I need to average only the LAST 30 lines (equivalent to the last 30 seconds) of a STEP. Or perhaps the LAST 100 lines of a step. (A very reduced sample type is below. In reality, my list of data is upwards for 110K rows long.)
I am looking to find a way to “filter out” (like a pivot table might) the average of the data for a given number of required lines (seconds). My first thought was to use xlookup with a filter function, and perhaps the address function, but I am coming up blank on how to do this. Any help would be appreciated.
I hope this truncated example helps.
I would want the output to look like (average of the rows highlighted above):
If I can only specify a fixed number of rows to average, I can handle that. The bonus would be that I can use an input field to denote which STEPs to average and how many rows at the end of the STEP to use.
I am looking to find a way to “filter out” (like a pivot table might) the average of the data for a given number of required lines (seconds). My first thought was to use xlookup with a filter function, and perhaps the address function, but I am coming up blank on how to do this. Any help would be appreciated.
I hope this truncated example helps.
Time | Temp | weight | depth | height | distance | Step # |
0 | 102.684514 | 15 | 6 | 10 | 4 | 0 |
1 | 102.684514 | 16 | 5 | 12 | 1 | 0 |
2 | 101.698675 | 16 | 6 | 15 | 2 | 0 |
3 | 101.764397 | 15 | 9 | 16 | 3 | 0 |
4 | 100.384222 | 14 | 5 | 1 | 6 | 0 |
5 | 102.717375 | 15 | 6 | 10 | 5 | 0 |
6 | 101.304339 | 15 | 4 | 11 | 9 | 0 |
7 | 101.370062 | 15 | 6 | 14 | 3 | 0 |
8 | 101.862981 | 14 | 9 | 12 | 2 | 0 |
9 | 102.58593 | 16 | 6 | 13 | 6 | 0 |
10 | 101.238616 | 15 | 8 | 15 | 4 | 1 |
11 | 100.647113 | 16 | 5 | 19 | 8 | 1 |
12 | 101.205755 | 14 | 6 | 10 | 5 | 1 |
13 | 101.83012 | 15 | 6 | 14 | 2 | 1 |
14 | 101.041448 | 14 | 9 | 15 | 1 | 1 |
15 | 100.975726 | 16 | 5 | 12 | 4 | 1 |
16 | 101.140032 | 15 | 4 | 14 | 9 | 1 |
17 | 101.370062 | 18 | 8 | 15 | 1 | 2 |
18 | 100.910003 | 19 | 5 | 12 | 2 | 2 |
19 | 100.679974 | 15 | 6 | 16 | 6 | 2 |
20 | 99.004047 | 16 | 9 | 13 | 9 | 2 |
21 | 102.224456 | 11 | 5 | 13 | 5 | 2 |
22 | 100.811419 | 13 | 4 | 12 | 3 | 2 |
23 | 100.515667 | 15 | 5 | 12 | 4 | 2 |
24 | 100.614251 | 18 | 6 | 14 | 6 | 2 |
25 | 102.060149 | 19 | 5 | 14 | 6 | 2 |
I would want the output to look like (average of the rows highlighted above):
Average of Temp | Average of weight | Average of depth | Average of height | Average of distance | Step # |
101.9397 | 15 | 7 | 13 | 3.7 | 0 |
101.2468 | 15 | 6 | 14 | 4.0 | 1 |
101.2452 | 15 | 5 | 13 | 4.8 | 2 |
If I can only specify a fixed number of rows to average, I can handle that. The bonus would be that I can use an input field to denote which STEPs to average and how many rows at the end of the STEP to use.
