Averages excluding certian data

[bbddvv]

I have a table with students listed vertically and their scores on 5
different tests listed horizontally.

Example (the real data is about 350 times bigger):
Name ... test1 ... test2 ... test3 ... test4 ... test5 ...etc
Sally ... 98 ... 70 ... 88 ... 85 ... 87
Bob ... 63 ... 84 ... 78 ... 81 ... 90
John ... 91 ... 87 ... 82 ... 87 ... 70

I know how to take the class average score on each test. But, how do i find
the average score for each test exluding a each student ***WITHOUT** changing
the cell range for each formula. Changing the cell range for each
"=average()" formula is too labor intensive for a data set of 350 students
and 80 tests.

Example:
class avg ex (student) ... test1 ... test2 ... test3 ... test4 ... test5 ...
etc
Sally ... ? ... ? ... ? ... ? ... ?
Bob ... ? ... ? ... ? ... ? ... ?
John ... ? ... ? ... ? ... ? ... ?

I'm sorry if this is confusing, any help would be apprecaited,
Brad

 

[Bob Phillips]

Brad,

If I correctly understand, you need

=AVERAGE(IF(name_rng<>"Bob",test1_rng))

which is an array formula, which means that you commit it with
Ctrl-Shift-Enter, and that you cannot use complete columns, but you musat
specify the cells (A2:A200 for example), and the ranges must be of the same
size.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[bbddvv]

Okay, that worked BUT how do i take into account blank cells (i.e. cells
where a student was not required to take a certain test). These blank cells
are being counted as zeros in the averaging formula, thus skewing the results
(they should simply not be counted)

 

[bbddvv]

I guess what i'm asking is, how do i get this array formula to ignore blank
cells when it averages the data?

 

[Peo Sjoblom]

Add a second check


=AVERAGE(IF((Name_Range<>"Bob")*(Test_Range<>""),Test_Range))

 

[bbddvv]

that didn't work, Peo.

Add a second check


=AVERAGE(IF((Name_Range<>"Bob")*(Test_Range<>""),Test_Range))



--

Regards,

Peo Sjoblom

 

[Bob Phillips]

In what way? What did you get?

Your original data didn't have any blanks. Can you show the data and formula
that didn't work?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[bbddvv]

The data has occasional blanks in it becasue not all students are required to
take all of the tests. Bob, the formula you gave worked great when every
student took a specific exam. However, when there was a blank cell (i.e. no
exam taken), the formula treated the cell as a zero value instead of just
igonring it. this obiviously skewed the results

 

[Bob Phillips]

Yeah, I get that, but Peo showed you how to get around that. You said that
that didn't work, so show us the data and the formula that you used.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[bbddvv]

Hi Bob. Here is the formula i used.

{=AVERAGE(IF((C2:C16<>"Bob")*(C2:C16<>""),H2:H16))}

Like i said, this treated the blank cells like zeros.

 

[Bob Phillips]

Brad,

That explains it perfectly.

You don't test the names for blank, but the scores,, so the array formula
should be

AVERAGE(IF((C2:C16<>"Bob")*(H2:H16<>""),H2:H16))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[bbddvv]

Well, this old dog just learned a new trick. You guys are great thanks