averaging a score for a team of people

[silentcoates]

Hello

please can you help....

In column A I have a list of dates, column B a list of names in no particular order and in column c i have a list of scores.

on sheet 2 ( called Lynnes team) I have a list of 6 people all of which appear in column B in sheet 1 (called input sheet) I need a total cell for that team that averages there scores. Column C will also have balnk cells and I need the formula to ignore blank cells and only average actual scores.

oh and one last thing it has to be dependent on dates. in sheet 1 I have two dates (a from and too date) in cells F2 and G3. I need the formula to look at column a and only average scores between the two dates in F2 and G2.

hope that all makes sense.

 

[FrankFahrendorf]

Hi SilentCoates

I think you want to do something like this:

A B C D E F G
1 date name score 04-03-2005 06-03-2005
2 01-03-2005 John 3 John 7
3 02-03-2005 Alice 2 Alice 9
4 03-03-2005 Mike 5 Mike 6
5 04-03-2005 John 6
6 05-03-2005 Kim 3
7 06-03-2005 Sean 2
8 07-03-2005 Maria 6
9 01-03-2005 Dennis 7
10 02-03-2005 John 3
11 03-03-2005 Mike 2
12 04-03-2005 Alice 1
13 05-03-2005 Maria 5
14 06-03-2005 John 0
15 07-03-2005 Kim 8
16 01-03-2005 Sean 9
17 02-03-2005 Mike 3
18 03-03-2005 Alice 4
19 04-03-2005 Mike 6
20 05-03-2005 Alice 8
21 06-03-2005 Maria 5
22 07-03-2005 Maria 3
23 01-03-2005 Dennis 2
24 02-03-2005 John 6
25 03-03-2005 Sean 8
26 04-03-2005 Maria 9
27 05-03-2005 Dennis 0
28 06-03-2005 John 1
29 07-03-2005 Alice 0

How did I get John 7, Alice 9 and Mike 6:

I used array formulas. Do this:

next to John put this formula =SUM(($A$2:$A$29>=$F$1)*($A$2:$A$29<=$G$1)*($E$2=$B$2:$B$29)*$C$2:$C$29)

but instead of just hitting enter, do CTRL SHFT ENTER

Best regards

Frank

 

[silentcoates]

Hello,

this does not work, please imagine a team made up of the follwoing names from your list
John
Alice
Sean
I need a formula that looks at all there scores within a certain date period and then averages their scores to provide an average for that team. But it also has to ignore an 0 or blank scores

 

[FrankFahrendorf]

OK I think I see what you want.

You still need the sum formula I gave, but you also need to count the number of scores different from 0 or blank. You can use the formula as for sum:

=SUM(($B$2:$B$29>=$E$1)*($B$2:$B$29<=$F$1)*($E2=$C$2:$C$29)*(D2:D29>0))

That should give you something like this:

As per example above you have

John 7 2
Alice 9 2
Sean 2 1

Now you can make a weighted average (Assume John is in cell E2):

=SUMPRODUCT(F2:F4/G2:G4,G2:G4)/SUM(G2:G4)

that gives 3.6.

You cannot do all of the above in one cell unless you write a macro.

 

[Peter_SSs]

silentcoates

If I have understood correctly, and you are just looking for the average for the team between two dates, then see if this is what you want.

The dates in question are in F2 and G2 on 'Input Sheet'. I have repeated the dates in F2 and G2 on 'Lynnes Team' but these repeated dates are for information only and are not used in the formula.

On 'Input Sheet' I have manually highlighted the column C cells that are between the dates and belong to members of Lynnes team. Again this was just to help in my checking. In my example then, I am trying to get the formula to average 5, 8 and 9 (ignoring the blank).

So here is the Input Sheet:

Mr Excel.xls
	A	B	C	D	E	F	G
1	Date	Name	Score			From	To
2	01/11/2005	Alice	1			04/11/2005	10/11/2005
3	02/11/2005	Bill	2
4	03/11/2005	Alice	3
5	04/11/2005	Don	4
6	05/11/2005	Sean	5
7	06/11/2005	Alice
8	07/11/2005	Tom	7
9	08/11/2005	Sean	8
10	09/11/2005	Alice	9
11	10/11/2005	John	10
12	11/11/2005	Ann	11
13	12/11/2005	Bill	12
Input Sheet

And here is 'Lynnes Team' sheet. The formula in F6 is:
=AVERAGE(IF(('Input Sheet'!$A$2:$A$13>'Input Sheet'!$F$2)*('Input Sheet'!$A$2:$A$13<'Input Sheet'!$G$2)*('Input Sheet'!C2:C13<>""),IF(ISNUMBER(MATCH('Input Sheet'!$B$2:$B$13,$A$2:$A$4,0)),'Input Sheet'!$C$2:$C$13,"")))
which must be confirmed with CTRL+SHIFT+ENTER

Mr Excel.xls
	A	B	C	D	E	F	G
1	Lynnes Team					From	To
2	John					04/11/2005	10/11/2005
3	Alice
4	Sean					Average for this team
5						between above dates
6						7.33333333
7
Lynnes Team