Excel 97 - Performance Projections

[Damaeus]

I'm using Excel 97 SR-1 to keep track of wages, tips, and compensation
for my job from week to week. I have it set up so I can enter my work
schedule and the hours I actually worked, as well as the cash I bring
home, the number of orders handled, etc... and it breaks this down
separating tips from compensation.

I'd like to find a simple way to have the spreadsheet examine past
performance to create projections for the future scheduled weeks.

I should say, also, that I've just moved all data and formulas from MS
Works to Excel 97 because I was finding the spreadsheet in Works rather
limiting in some ways.

In Works, to get projections I would have to actually go in and type
something like this:

=(($W$3+$AT$12+$AT$20+$AT$28+$AT$36+$AT$44)/6)*U51

This would project the amount of tips I'd make in the current scheduled
day, based on the last six weeks averages. Each day of the following
week's projection formula would look like this:

=(($W$3+$AT$12+$AT$20+$AT$28+$AT$36+$AT$44+$AT$52)/7)*U53
=(($W$3+$AT$12+$AT$20+$AT$28+$AT$36+$AT$44+$AT$52+$AT$60)/8)*U61
=(($W$3+$AT$12+$AT$20+$AT$28+$AT$36+$AT$44+$AT$52+$AT$60+$AT$68)/9)*U71

....and so forth, each week resulting in a longer and longer formula.

It would be much more efficient if I knew of a way to simply run through
and add up every eighth cell in a column and divide by the number of
cells that were added together instead of having to manually alter the
formula each week.

I'd also like to use a similar method to keep a running average of
monies earned per hour. The spreadsheet is for the entire year, but
while figuring the running average, I would want it to exclude weeks
which have not yet been worked, nor would I want it to include days I
didn't work at all. I know how to use statements like:

=IF(f5=0,0,F5+G5)

But I'd need something more robust than that simple statement to
accomplish my wishes in the last paragraph.

What do you suggest? If you need to see a copy of the spreadsheet I
have set up, I can provide one.

Thanks,
Damaeus

 

[Don Guillett]

try this idea to sum every 8th item in the column D.
=SUMPRODUCT((MOD(ROW(D1100)*1,7)=1)*D1100)