Calculating average scores from multiple sheets' information

[quailhunter]

I'd like to create a sheet that shows average scores for each individual with scores in other worksheets. This 'averages' sheet is in sorted name sequence, as are all of the score sheets.

1. Using the name of the individual on the 'Averages' sheet, I'd like to find all scores in one specific column across all other scoring sheets.
2. Also, if a sheet contains no score (blank or zero) for that individual, then don't factor that into the average. Using just this AVERAGE function below, returns an invalid average if an individual has only 2 out of 3 scores:

=AVERAGE(Week03:D4Week01!D6)


I'm thinking I need to use a VLOOKUP function (SUM also???), but haven't had much success getting past the invalid syntax message when attempting to nest the function.

[Peo Sjoblom]

If you use blanks as opposed to zero this should work

=AVERAGE(Week01:Week03!D4:D6)

obviously it will give another answer if any of the cells are zero
however average disregards blank cells

there are ways of using conditions like >0
but a good rule of thumb
is to design the workbook so you can use the most simple
formulas since they are both faster and easier to audit



--
Regards,

Peo Sjoblom

(No private emails please)


"quailhunter" <[email protected]>
wrote in message
news:[email protected]...
>
> I'd like to create a sheet that shows average scores for each individual
> with scores in other worksheets. This 'averages' sheet is in sorted name
> sequence, as are all of the score sheets.
>
> 1. Using the name of the individual on the 'Averages' sheet, I'd like
> to find all scores in one specific column across all other scoring
> sheets.
> 2. Also, if a sheet contains no score (blank or zero) for that
> individual, then don't factor that into the average. Using just this
> AVERAGE function below, returns an invalid average if an individual has
> only 2 out of 3 scores:
>
> =AVERAGE(Week03:D4Week01!D6)
>
>
> I'm thinking I need to use a VLOOKUP function (SUM also???), but
> haven't had much success getting past the invalid syntax message when
> attempting to nest the function.
>
>
> --
> quailhunter
> ------------------------------------------------------------------------
> quailhunter's Profile:
> http://www.excelforum.com/member.php...o&userid=27739
> View this thread: http://www.excelforum.com/showthread...hreadid=476554
>

[quailhunter]

From the D4 cell on the 'Averages' sheet, how would I get the average of all scores in column D4 across all 3 sheets (week03-week01) for the individual named in the Averages sheet (A4)? All sheets are sorted by name, but each sheet may contain not all names on the Averages sheet.