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(Week034Week01!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!D46)

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]...

 

[quailhunter]

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