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

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"
wrote in message
...

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

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.


--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile: http://www.excelforum.com/member.php...o&userid=27739
View this thread: http://www.excelforum.com/showthread...hreadid=476554