averaging a score for a team of people

silentcoates

New Member
Joined
Oct 11, 2005
Messages
41
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
ABCDEFG
1DateNameScoreFromTo
201/11/2005Alice104/11/200510/11/2005
302/11/2005Bill2
403/11/2005Alice3
504/11/2005Don4
605/11/2005Sean5
706/11/2005Alice
807/11/2005Tom7
908/11/2005Sean8
1009/11/2005Alice9
1110/11/2005John10
1211/11/2005Ann11
1312/11/2005Bill12
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
ABCDEFG
1Lynnes TeamFromTo
2John04/11/200510/11/2005
3Alice
4SeanAverage for this team
5between above dates
67.33333333
7
Lynnes Team
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top