Exercise log: how to calculate average miles per week?

Hi George,

My personal rule of thumb is that four miles of biking equates roughly to 1 mile on foot. I guess it depends on your styles of biking and walking.

Where do you want these weekly averages to be in your table?

Jerry

George,

You will be using the AVERAGEIFS function. Let's say your summary table has the starting date for each week, you would calculate the average for all dates greater than the summary date that are also less than the next summary date. Easy. Check out the function and if you have questions, let us know.

Jerry

George

Mock-up a table for your averages, post it, and we'll give you help with the formulas.

Jerry

George,

for "Walking weekly average miles" use

=Exercise log details :: E2/COUNTA(Exercise log details :: E)

this is actually the sum of all miles walked divided by the number of days you walked.

I am not sure how this would be different from averaging the average of the weeks

You can also add a new column to calculate an average on a weekly basis:

Where the formula in G9 is:

G9=SUM(E3:E9)/7

a couple notes. I added rows at the beginning and end so there are always full weeks. select G9, copy, then paste at each of the sunday rows in the G column

now you can average column G for the number of miles walked in a week by updating the formula in cell A1 in the table "Walking weekly average miles"

A1=AVERAGE(Exercise log details :: G)

George Coghill wrote:

Jerry — thanks so much! I'm kind of new to Numbers & spreadsheets (but I love them!). Here's a Dropbox link to a sample Numbers file for the exercise log as I am using it now: https://dl.dropbox.com/u/28419/Exercise%20%26%20Health%20Log%20copy.numbers

Thanks again!

George,

Take a look at this and see if it's going in the right direction...

https://dl.dropbox.com/u/9499157/Exercise%20%26%20Health%20Log%20-%20revJDG.numb ers

I gave you a table of weekly averages with Grand Averages in the second header row of the Week Averages table.

Jerry

Hi George,

Here's one way, using two added columns on your 'details' table to calculate the weekly totals for walking and cycling, then use those to calculate the weekly averages, reported in row 2 of that table.

The two averages may then be transfered to the Walking/Cycling Weekly Average tables on the Overview sheet.

Except for column references, the formulas in columns G (Walking miles/week) and H (cycling miles/week) are the same.

G2: =AVERAGE($G)

H2: =AVERAGE($H)

G3: =IF(AND(WEEKDAY(A)=7,COUNTIFS($E,">0",$A,">"&A3-7,$A,"<="&A3)>0),SUMIFS($E,$A," >"&A3-7,$A,"<="&A3),"")

H3: =IF(AND(WEEKDAY(A)=7,COUNTIFS($F,">0",$A,">"&A3-7,$A,"<="&A3)>0),SUMIFS($F,$A," >"&A3-7,$A,"<="&A3),"")

Formulas in G3 and H3 are filled down to the end of their respective columns. These columns may be hidden to give some protection to the formulas.

As written, the formulas in row 3 (and below) calculate the week's totals for the seven day period ending Saturday, which, for the sample data, results in a lowered average for the walking mileage. I would suggest either deleting rows 3 and 4 from the table, or changing "WEEKDAY(A)=7" to WEEKDAY(A)=5 to ensure that that every 'week' on the table has a full seven days (either Sunday through Saturday, starting June 3, or Friday through Thursday, starting June 1).

Regards,

Barry

Hi George,

G3 should remain blank as row 3 is not a Saturday.

G4, when the formula is filled down to that row, should show 2.3, the total miles walked in the week ending Saturday June 2.

Here's the formula, with a breakdown of what it does:

G3: =IF(AND(WEEKDAY(A)=7,COUNTIFS($E,">0",$A,">"&A3-7,$A,"<="&A3)>0),SUMIFS($E,$A," >"&A3-7,$A,"<="&A3),"")

IF(condition,doifTRUE,doifFALSE)

condition: AND(condition 1,condition 2)

TRUE only if both condition 1 AND condition 2 are TRUE

condition 1: WEEKDAY(A)=7

TRUE only if the date on this line is a Saturday

condition 2: COUNTIFS($E,">0",$A,">"&A3-7,$A,"<="&A3)>0

COUNTIFS(test values 1,condition 1,test values 2,condition 2,test values 3,condition 3)

Counts only the rows where:

the numerical value in column E is greater than zero (ie. a number has been entered in the miles walked column)

AND the date in column A is after the date seven days before the date in the row containing the formula

AND the date in column A is on or before before the date in the row containing the formula

TRUE only if COUNTIFS returns a count of one or more.

doifTRUE: SUMIFS($E,$A," >"&A3-7,$A,"<="&A3)

sums the values in column E in rows for dates fitting the same conditions for dates in COUNTIFS above.

doifFALSE: ""

inserts a null string (which dispalys as an 'empty' cell) if either of the conditions in AND() is not TRUE.

Regards,

Barry