Forum Discussion
Averaging non-adjacent cells with earlier errors
Wow! That must come close to an all-time record for a calculation-hostile data layout!
For me, using Names to identify meaningful data objects, coping with data arrays scattered every 5rd column and every 3rd row with non-related data interspersed is a challenge.
Even the traditional direct selection of data using mouse clicks hits issues because the columns are broken up by merged cells.
One comment I would offer is do not attempt to calculate the week average speed from daily averages (the process requires a weighted harmonic mean). Instead, calculate the value you require from the total distance and time taken.
= SUM(Distance) / SUM(WalkTime) / 24
Similarly the equivalent speed, allowing for the presence of hills, would be
= SUM( Distance+7.92*Elevation/5280 ) / SUM(WalkTime) / 24
Collecting the terms for the named variables such as 'distance' is somewhat more painful, For example 'Distance' itself refers to a relative column with LOOKUP used to select every third value
= LOOKUP(day, seq, Experiment!D$20:D$40)
More complicated formulas would be required to lookup number by week number as well as day but, in the main, I think relative formulas that repeat over weeks will do.