Looking for what's not there

[Sivodsi]

Hi Dave,
Thanks for the reply. I'll try and clarify things.

My test results page has no missing id numbers. It has the populatio
of students and their test scores from a test that everybody took.

My survey results has the missing data. I used vlookup to get a
average test score for those whose ID numbers I have got.

What I need to do is find out if those who did not put in their I
number do not significantly differ from those who did put their ID no
in the survey. To do this I can use a t-test. For the t-test to work
have to enter two ranges of figures. The first range will be those wh
did put their ID number in, no problems there. But I need to find a wa
to gather the scores of all those who did not put their id numbers i
the survey so that I can put their scores in the t-test formulae.

If I did not have excel I would check the list of id numbers on th
survey against the test results and simply cross off all those who pu
their id numbers in the survey, then I could enter those I did no
cross off into the t-test. But I have the advantage of sophisticate
software of excel - which I do not know how to use!
;-)

Thanks for your help, I really appreciate it.

Davi

 

[Dave Peterson]

There's a function that's pretty similar to =vlookup(). But it just checks to
see if there was a match between two columns of data (on separate worksheets in
separate workbooks is ok).

It's called =match(). It actually returns a number (the offset into the second
list) if it's found. If it's not found, it'll return an error. (Kind of like
=vlookup() with the error.)

So if you wanted to determine if one item was on another list, you could use
this formula:

=if(isnumber(match(a2,sheet2!a:a,0)),"On List","Not On List")

Once you have that column, couldn't you determine stats based on it?

Data|pivottable is one way to get the averages/counts/etc.

Another way would be to sort by that column and then do Data|subtotal.

If you want to try the pivottable stuff, you may want to look at somee links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx