Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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) |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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? |
#9
|
|||
|
|||
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? |
#10
|
|||
|
|||
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. |
#11
|
|||
|
|||
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. |
#12
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averages excluding #N/A | Excel Discussion (Misc queries) | |||
Trendline excluding last 2 data points of series | Charts and Charting in Excel | |||
Pull Current Month's Data Out of List - Repost | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |