Looking for what's not there

[Sivodsi]

Hi there,
This is a repost of a problem I've already asked about, but it has (
hope) a better explanation.

I have two databases, firstly a test results for all the students at m
university who took the test, here each students test results are laye
out like this:

Id Number/ Reading / Listening / Grammar / Total
.......20477.......10.......12.......13..............35
.......20478.......13.......13.......10..............36
.......20479.......13.......13.......10..............36
etc

The second one is a survey I gave all of the student. Unfortunately no
all of them put in their id number. The survey is layed out like this:

ID number /Years of Engle ed / Weeks study abrd/Read/ List/ Gram/ Tot
.......20477..............4.....................2 .
.............10..............12..........13....35
.......XXXXX..............4.....................0
..............#N/A..........#N/A...#N/A...#N/A
.......20479..............0.....................12
..............13..............13...........10.....36

I used vlookup to find out the test result of those who put their I
number in, and obviously it is impossible to find out the test resul
of the students who didn't enter their id number.

But what I can do is find out if the average of the students who put i
their id number is significantly different to those who did not pu
their id numbers in. All I have to do is get the average of those whos
id numbers I have and the average of those whose ID number I don't have
and run a t-test on them, which will tell me if the mean i
significantly different.

The problem is, how do I get the scores of those who did not put thei
id number in the survey. I know it is logically feasible, but how do
do it in excel?

Thanks in advance for your help! Really looking forward to you
answer.
Davi

 

[Dave Peterson]

I'm not sure if I understand. But in your test results worksheet, do you have
the values for the students who didn't put in their id's?

Id Number/ Reading / Listening / Grammar / Total
.......20477.......10.......12.......13..............35
.......20478.......13.......13.......10..............36
.......20479.......13.......13.......10..............36
.....missing.......11.......11.......11..............11
(some indicator??)

If yes, you could add another column with a formula like:

=if(a2="missing","UnKnown","Known")
and drag down.

Then you could use data|pivottable and use that column as the row field.

And drag the headers for each column into the data field, double click on each
and choose average (it will initially show either "Count of" or "Sum of"
depending on whether all the data is numeric.