Averages excluding certian data
 

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

Averages excluding certian data
 

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

Averages excluding certian data
 

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)


"Bob Phillips" wrote:

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

Averages excluding certian data
 

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


"bbddvv" wrote:

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)

Averages excluding certian data
 

Add a second check


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



--

Regards,

Peo Sjoblom


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


"Bob Phillips" wrote:

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

Averages excluding certian data
 

that didn't work, Peo.

"Peo Sjoblom" wrote:

Add a second check


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



--

Regards,

Peo Sjoblom


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


"Bob Phillips" wrote:

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

Averages excluding certian data
 

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" wrote in message
...
that didn't work, Peo.

"Peo Sjoblom" wrote:

Add a second check


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



--

Regards,

Peo Sjoblom


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


"Bob Phillips" wrote:

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

Averages excluding certian data
 

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" wrote:

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?

Averages excluding certian data
 

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

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?

Averages excluding certian data
 

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" wrote:

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.

Averages excluding certian data
 

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

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.

Averages excluding certian data
 

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

"Bob Phillips" wrote:

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

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.