Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Please Help! Can't solve excel problem
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. |
#2
|
|||
|
|||
Quote:
Let me know if you need anything explaining. If this is along the right line of what you're after, I'm more than happy to help you develop it to meet all your needs. S. Last edited by Spencer101 : July 30th 12 at 06:37 PM |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please Help! Can't solve excel problem
On Mon, 30 Jul 2012 03:27:33 +0000, sully017 wrote:
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please Help! Can't solve excel problem
On Sunday, July 29, 2012 10:27:33 PM UTC-5, sully017 wrote:
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. -- sully017 You should always provide a meaningful subject line. If desired send file to dguillett1 @gmail.com with complete details |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help me solve my problem | Excel Programming | |||
excel function solve problem? | Excel Worksheet Functions | |||
CAN ANYONE PLEASE SOLVE MY PROBLEM | Excel Programming | |||
How to solve a complex problem using Excel 2003 | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |