Averaging non-adjacent cells with earlier errors

Copper Contributor

I'm an Excel newbie and have been butting my head against the wall. Not sure where the problem is, so have attached the spreadsheet. 

 

The explanation for the spreadsheet is below. My question is at the end.

 

The attached spreadsheet is to track health issues.  Most of the first cells for each type of item/data have the red triangle showing a comment, with the comment explaining what the cell is for. I've entered sample data numbers in some cases to make it more obvious what is happening. Below is my documentation for the spreadsheet.

 

(NOTE) Naismith/Scarf is a method to take hills into account when comparing different walking route distances. It is just a fudge factor for the elevation change, based on observations by experienced hikers. It adds distance to the actual total, based on how much uphill walking there was. This creates an "equivalent distance." The equivalent distance (in terms of effort expended) is what would be an equivalent distance that is flat (no hills).

 

Spreadsheet is designed to be able to enter data on some days and not on others and have calculations still work.

 

Column 1 serves as a legend or list of abbreviations.

 

In Row 1, (C1, H1 etc) show the week number, starting with WEEK ZERO.

(E1,F1,G1) J1,K1,L1) etc are column titles. So E3-E9 record morning resting pulse. F3-F9 records Systolic Blood Pressure. G3-G9 records Diastolic Blood pressure.

 

Row 2 sets the calendar date for the week (date of Sunday, which starts the week).

 

Rows 3-9 are Sunday through Saturday for each week. There are then four columns per week.

 

Rows 10-17 sum up or average the data for the week for the weigh, weight change, pulse and BP.

 

Rows 20-24 record behaviors. eg:

If I walk, D20 records the distance. E20 records the elevation change on the walk (from Google Earth). F20 records the time. C20 calculates the speed in mph. D21 calculates the equivalent distance if it had been flat (no hills) using Naismith/Scarf equivalence constant. C21 then calculates the speed (mph) over the Scarf equivalent distance. This lets me see progress in speed and distance even when I change routes with different amounts of hills.

 

E21 records the duration of morning stiffness.

 

F21 records (1-10 scale) the severity of stiffness.

 

G21 records the exercise intensity of the day before to show if there are correlations with E21 and F21.

 

C22 is a comment section to record diet and types of exercise other than walking.

 

************************

MY QUESTION

I want to put week averages in Rows 42-43. Cells C47-51 work fine. The problem is when I started on cell C44.

 

I wanted to be able to average the weekly values of walking speed, distance, equivalent speed and equivalent distance. C44 is to average weekly walking speed.

 

To average the weekly speed of walking, I want to average whatever walking I did that week. Since walking could take place any day of the week, for the first week, it would average any values in C21, C23, C26, C29, C32, C35, C38. The problem is, because not every day has entries for walking, many of those cells return a "#DIV/0!" error. This means averaging doesn't work. I tried putting in "<>0" to exclude those, but apparently don't understand how that works. I am guessing it only excludes actual zero values and not the error messages as well. I also tried ">0." Also tried AVERAGEIF (got "Too many arguments.") and also got lost in trying to understand arrays. 

 

I don't mind a "#DIV/0!" error in rows 22-40 or in rows 10-18 for weeks not yet entered. Those are fine. But in rows 43-46 it will mean I don't get the averages for the week, which is what I want to be able to see trends.

 

Is there a solution for this problem? If you can show me what it is, I can use it for the other averages (C43,C45,C46) as I know they are going to have the same problem, and then copy the whole section into following weeks.

 

I figure this can't be the first time this problem has come up, so there is likely an easy (and well known) solution that may even be well known.

 

Would someone point it out?

 

Thanks

 

Chuck

2 Replies
I suggest that you manually enter your desired results, with an explanation of how you arrived at each of those results. Thereafter, I will analyze such results and recommend the formulas you need for each of them. Most importantly, please modify the layout of your data by unmerging all data cells.

@chuck1801 

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.