auto-update cells with this week/last week's totals?

[Marathon Man]

Hi there!

I have 3 cells - C2, D2 & E2.

C2 contains the formula =SUM(C1528:C1534).
D2 contains the formula =SUM(D15281534).
E2 contains the formul
=IF(ISERROR(AVERAGE(E1528:E1534)),"PACE!",AVERAGE(E1528:E1534))

C2 shows the total number of miles I have run from Monday of th
current week to Sunday inclusive.

D2 shows the total hours I have run over the same period of time.

E2 shows the average pace I've run at, again over the same period o
time. (I added the ISERROR function for neatness as I had to kee
amending the column width when I get the DIV0 error).

C3, D3 & E3 do a similar thing to C2, D2 & E2 but these figures relat
to LAST week from Mon to Sun.

I was wondering if there was a way of coding these 6 cells so I don'
have to keep manually updating them every week?

The column details are as follows:

COLUMN A (Date of Run) COLUMN B (Route) COLUMN C (Distance) COLUMN
(Time) COLUMN E (Pace)

(A=Sat, 07 Aug 2004) (B=Text) (C=4.0) (D=0:31:10) (E=7:48)


The info above relates to the latest row, 1533, but I add a row eac
time I've been running, so tomorrow (Sunday) it'll be 1534 and so o
for each run thereafter (but I may not be mad enough to go for a ru
every day).

As I said, I'm looking to see if it's possible for C2, D2 & E2 and C3
D3 & E3 to be coded so they'll auto-update when the latest 7 day
become the previous week's 7 days. At the moment, every Monday I hav
to manually update the formula to change the contents of row C2, D2
E2 to apply to C3, D3 and E3, and in turn update C2, D2 & E2 to reflec
the current week.

I need the cells in row 2 to reflect the totals of the rows from th
current week from Monday to Sunday, so it must not sum more than
rows, but also if I were to miss say 1 or 2 day's training, it would b
less.

I need the cells in row 3 to reflect the totals of the rows from th
previous week from Monday to Sunday, so it again must not sum more tha
7 rows, but also if I were to miss say 1 or 2 day's training, it woul
be less.

I hope this is possible to do either with a formula or VB - maybe b
making use of the dates in Column A?

TIA

 

[Earl Kiosterud]

Marathon Man,

How is the current week defined? You say it's the "current week from Monday
to Sunday." So if the current day is Wednesday, is it then from the
previous Monday to the following Sunday?

 

[Marathon Man]

Hi Earl - thanks for helping me out!

I'm not sure if I've understood you correctly, because if the curren
day is Wednesday, it will always be from the current week.

The current week always starts Monday and finishes Sunday - as you ma
have worked out already, my current formula involves cells that ar
currently empty, awaiting entries for the rest of the current week.
The total for this week, however, can be transferred to last week onc
Sunday's data has been entered or when it's Monday (whichever's easie
to formulate) - last week's data will only ever change once a week whe
the current week's data is complete.

Similarly, last Wednesday will always be from the previous week.

Hope that helps?

M

 

[Earl Kiosterud]

MM,

Formula for current week miles:
=SUMPRODUCT(($D$1-WEEKDAY($D$1,3)=(A9:A65000-WEEKDAY(A9:A65000,3)))*C9:C6500
0)

Formula for last week miles:
=SUMPRODUCT((($D$1-WEEKDAY($D$1,3)-7)=(A9:A65000-WEEKDAY(A9:A65000,3)))*C9:C
65000)

The times would be similar, using column D.

I have table starting in row 9. So miles are in C9 down, dates in A9 down.
I have the current date in D1. You could put =NOW() in it, or substitute
TODAY() for D1 in the formulas.

Not thoroughly tested.

 

[Earl Kiosterud]

MM,

Oops. I mean you could put =TODAY in D1, or substitute TODAY() for D1 in
the formulas. The formulas are array formulas -- use Ctrl-Shift-Enter.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

MM,

Formula for current week miles:
=SUMPRODUCT(($D$1-WEEKDAY($D$1,3)=(A9:A65000-WEEKDAY(A9:A65000,3)))*C9:C6500
0)

Formula for last week miles:
=SUMPRODUCT((($D$1-WEEKDAY($D$1,3)-7)=(A9:A65000-WEEKDAY(A9:A65000,3)))*C9:C
65000)

The times would be similar, using column D.

I have table starting in row 9. So miles are in C9 down, dates in A9 down.
I have the current date in D1. You could put =NOW() in it, or substitute
TODAY() for D1 in the formulas.

Not thoroughly tested.

 

[Marathon Man]

Hi Earl!

Thanks a lot for your help!

You said:

"I have table starting in row 9. So miles are in C9 down, dates in A
down.
I have the current date in D1. You could put =TODAY in D1, o
substitute TODAY() for D1 in the formulas."


Unfortunately I've not been able to get your formulas to work because:

1. My data starts in row 12. I substituted A12 for A9 in you
formulas but I got an error.

2. I've had to use A1 for =TODAY, but I get Invalid Name Error (I'
using Excel 2003). I tried also using TODAY() in the formulas but
again got an error.

Also, you said

"The formulas are array formulas -- use Ctrl-Shift-Enter."

Is there anything I should be doing with this information?

I'd be really grateful if you could amend the formulas for th
different cells!

Thanks for your help again

 

[Earl Kiosterud]

MM,

Make sure you have the current date, or =TODAY() in D1. If you move that
cell to A1, the formulas will adjust their references automatically, in the
usual way. Select it and drag it by its border (where the mouse pointer
becomes 4 arrows). The references to D1 should change to A1 in the formulas

If the use of TODAY() gives a name error, then your Excel doesn't seem to
have that function. I'm using Excel 2002, and Help on TODAY() doesn't
indicate it's supplied by an add-in, but maybe it is. Try using this
instead:
=INT(NOW())

You didn't say what error you got when you substituted row 12 for row 9, but
I've inserted three rows into my test sheet, moving the first data row to
row 12, and here are the resulting formulas.

Current week:
=SUMPRODUCT(($D$1-WEEKDAY($D$1,3)=(A12:A65003-WEEKDAY(A12:A65003,3)))*C12:C6
5003)
Prior week
=SUMPRODUCT((($D$1-WEEKDAY($D$1,3)-7)=(A12:A65003-WEEKDAY(A12:A65003,3)))*C1
2:C65003)

Since the formulas are array formulas, after entering them, or any edit of
them, press Ctrl-Shift-Enter instead of just Enter.

The dates should be in column A, and should be for-real Excel dates (if you
do Edit - Clear - Formats on Aug 9, 2004, it should result in 38208 -- if
not, it's not a real date. Same for A1. You can undo that to get the date
format back). The miles should be in column C, and should be ordinary
numbers, like 1, 4.6, etc.

It seems to be working here, so we probably just need to find some
difference in the setups. We should set up for the setups. <g>

If you ever run past row 65000, this will fail. You'll be dead by then from
that, and I'll be nuts, so we shan't worry over that.

 

[Marathon Man]

Hi Earl!

I've just read your reply - thanks a lot!

I'm away from my home PC at the moment but I'll check out your
suggestion over the weekend and let you know how how it goes.

Thanks once again!

MM

P.S. You said I'll be dead by 65,000 - the way my running's been going
in the 90 degree heat it'll be by row 1400

Cya!

 

[Marathon Man]

*MM,

Make sure you have the current date, or =TODAY() in D1. If you move
that
cell to A1, the formulas will adjust their references automatically,
in the
usual way. Select it and drag it by its border (where the mouse
pointer
becomes 4 arrows). The references to D1 should change to A1 in the
formulas

If the use of TODAY() gives a name error, then your Excel doesn't
seem to
have that function. I'm using Excel 2002, and Help on TODAY()
doesn't
indicate it's supplied by an add-in, but maybe it is. Try using
this
instead:
=INT(NOW())

You didn't say what error you got when you substituted row 12 for row
9, but
I've inserted three rows into my test sheet, moving the first data
row to
row 12, and here are the resulting formulas.

Current week:
=SUMPRODUCT(($D$1-WEEKDAY($D$1,3)=(A12:A65003-WEEKDAY(A12:A65003,3)))*C12:C6
5003)
Prior week
=SUMPRODUCT((($D$1-WEEKDAY($D$1,3)-7)=(A12:A65003-WEEKDAY(A12:A65003,3)))*C1
2:C65003)

Since the formulas are array formulas, after entering them, or any
edit of
them, press Ctrl-Shift-Enter instead of just Enter.

The dates should be in column A, and should be for-real Excel dates
(if you
do Edit - Clear - Formats on Aug 9, 2004, it should result in 38208
-- if
not, it's not a real date. Same for A1. You can undo that to get
the date
format back). The miles should be in column C, and should be
ordinary
numbers, like 1, 4.6, etc.

It seems to be working here, so we probably just need to find some
difference in the setups. We should set up for the setups. <g>

If you ever run past row 65000, this will fail. You'll be dead by
then from
that, and I'll be nuts, so we shan't worry over that.

Hi Earl!

Mixed results I'm afraid!

I tried the TODAY() again and it works fine now, but...

I tried out your formula for current week's mileage in cell C2 as
follows:

=SUMPRODUCT(($D$1-WEEKDAY($D$1,3)=(A12:A65003-WEEKDAY(A12:A65003,3)))*C12:C65003)

I entered it keying ctrl-shift-enter as you said, but I got the
'#value' error. I clicked on the 'trace precendents' button and it
points to A12 & C12.

I didn't try the previous week's mileage as it seemed a bit pointless
until this problem is resolved...

Does it make a difference that the first date in A12 is 01.01.1998?

Thanks again for your help Earl!

 

[Earl Kiosterud]

MM,

You may want to reduce the list to a few or even one row as you debug this.
Look at the things I mentioned in my last post carefully and see if it leads
you to anything. Be sure to use Ctrl-Shift-Enter any time you edit those
formulas.

My email address is below. Decode it and send the file if you want.