Please Help! Can't solve excel problem

[sully017]

I've dabbled in excel over the years but have hit a cross roads. I hel
teach a middle school track summer camp. Now every week we do certai
timed activities and keep track of the results. Here is the problem-
can't find a way to average these results.

My excel sheet is set up with 5 sheets. Sheets 1-4 Being labeled Week1
Week2, Week3, Week4 and sheet 5 is for my calculations. Each sheet i
set up identical with students name in column A (i.e. John, Tim, Sarah
Connie) and the timed events in the remaining columns (ex. 100m, 200m,
cone drill, etc, etc)

Now I would like to have a master sheet that will tell me Johns (an
every other students) average time for each event across the 4 wee
period. (i.e. average time for 100m, 200m, 3 cone drill, etc) Th
problem i seem to be running into is that John is not always in cell A
one week he will be in cell A1 the next week he will be in cell A19.

Any help would be appreciated

 

[Spencer101]

I've dabbled in excel over the years but have hit a cross roads. I hel
teach a middle school track summer camp. Now every week we do certai
timed activities and keep track of the results. Here is the problem-
can't find a way to average these results.

My excel sheet is set up with 5 sheets. Sheets 1-4 Being labeled Week1
Week2, Week3, Week4 and sheet 5 is for my calculations. Each sheet i
set up identical with students name in column A (i.e. John, Tim, Sarah
Connie) and the timed events in the remaining columns (ex. 100m, 200m,
cone drill, etc, etc)

Now I would like to have a master sheet that will tell me Johns (an
every other students) average time for each event across the 4 wee
period. (i.e. average time for 100m, 200m, 3 cone drill, etc) Th
problem i seem to be running into is that John is not always in cell A
one week he will be in cell A1 the next week he will be in cell A19.

Any help would be appreciated.

Hi,
Sounds easy enough to resolve, but I will need a dummy workbook. Coul
you post one here or email one to me at pubnut AT gmail DOT com ?

For confidentiality purposes you may wish to change the names. You ca
just swap them to Name1, Name2 etc

 

[Spencer101]

I've dabbled in excel over the years but have hit a cross roads. I hel
teach a middle school track summer camp. Now every week we do certai
timed activities and keep track of the results. Here is the problem-
can't find a way to average these results.

My excel sheet is set up with 5 sheets. Sheets 1-4 Being labeled Week1
Week2, Week3, Week4 and sheet 5 is for my calculations. Each sheet i
set up identical with students name in column A (i.e. John, Tim, Sarah
Connie) and the timed events in the remaining columns (ex. 100m, 200m,
cone drill, etc, etc)

Now I would like to have a master sheet that will tell me Johns (an
every other students) average time for each event across the 4 wee
period. (i.e. average time for 100m, 200m, 3 cone drill, etc) Th
problem i seem to be running into is that John is not always in cell A
one week he will be in cell A1 the next week he will be in cell A19.

Any help would be appreciated.

Here is a VERY basic example of what I think you mean.

Let me know if you need anything explaining.

S

+-------------------------------------------------------------------
|Filename: sully017 example.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=512
+-------------------------------------------------------------------

 

[Ron Rosenfeld]

I've dabbled in excel over the years but have hit a cross roads. I help
teach a middle school track summer camp. Now every week we do certain
timed activities and keep track of the results. Here is the problem- I
can't find a way to average these results.

My excel sheet is set up with 5 sheets. Sheets 1-4 Being labeled Week1,
Week2, Week3, Week4 and sheet 5 is for my calculations. Each sheet is
set up identical with students name in column A (i.e. John, Tim, Sarah,
Connie) and the timed events in the remaining columns (ex. 100m, 200m, 3
cone drill, etc, etc)

Now I would like to have a master sheet that will tell me Johns (and
every other students) average time for each event across the 4 week
period. (i.e. average time for 100m, 200m, 3 cone drill, etc) The
problem i seem to be running into is that John is not always in cell A1
one week he will be in cell A1 the next week he will be in cell A19.

Any help would be appreciated.

Problem is that AVERAGEIF, which you could use on a single sheet, does not accept 3D (multisheet) references.
So one way is to execute SUMIF and COUNTIF on each sheet, then divide the sum by the count to get the average.

Depending on exactly how your sheet is set up, something like this might work:

A2:
=(SUMIF(Week1!$A$2:$A$20,A2,Week1!B$2:B$20)+
SUMIF(Week2!$A$2:$A$20,A2,Week2!B$2:B$20)+
SUMIF(Week3!$A$2:$A$20,A2,Week3!B$2:B$20)+
SUMIF(Week4!$A$2:$A$20,A2,Week4!B$2:B$20)+
SUMIF(Week5!$A$2:$A$20,A2,Week5!B$2:B$20))/
(COUNTIF(Week1!$A$2:$A$20,A2) +
COUNTIF(Week2!$A$2:$A$20,A2)+
COUNTIF(Week3!$A$2:$A$20,A2)+
COUNTIF(Week4!$A$2:$A$20,A2)+
COUNTIF(Week5!$A$2:$A$20,A2))

The trick is to use addressing modes so the formulas will self-adjust as you fill right and/or down.

You could also try using the Pivot Table Wizard to set up a pivot table consolidating multiple ranges.

 

[Don Guillett]

I've dabbled in excel over the years but have hit a cross roads. I help

teach a middle school track summer camp. Now every week we do certain

timed activities and keep track of the results. Here is the problem- I

can't find a way to average these results.



My excel sheet is set up with 5 sheets. Sheets 1-4 Being labeled Week1,

Week2, Week3, Week4 and sheet 5 is for my calculations. Each sheet is

set up identical with students name in column A (i.e. John, Tim, Sarah,

Connie) and the timed events in the remaining columns (ex. 100m, 200m, 3

cone drill, etc, etc)



Now I would like to have a master sheet that will tell me Johns (and

every other students) average time for each event across the 4 week

period. (i.e. average time for 100m, 200m, 3 cone drill, etc) The

problem i seem to be running into is that John is not always in cell A1

one week he will be in cell A1 the next week he will be in cell A19.



Any help would be appreciated.

You should always provide a meaningful subject line. If desired send file to dguillett1 @gmail.com with complete details