Definining ranges with Logic "if" function

[Sivodsi]

Hi there,
I return once more to the fountain of xcel knowledge!

I am administering an exam to 800 students who fill in bubble cards, and sometimes making mistakes with their ID numbers (they are incoming freshmen).

We read the bubble cards in machine and then import the figures into excel. I want to put a check in to see if it is a valid ID number. I thought I could do this using an IF function. There are a limited range of differnt ID numbers for example:
2051001-2051396 for students majoring in French
2031801-2031835 for French majors who transferred
2052001-2052059 for Greek majors
2032801 2032802 for Greek majors who transferred from another major.
etc
there are 15 different ranges of ID numbers.

Question 1: I thought that I could use the IF function to alert me if an ID number is outside any of these ranges, but I cannot work out how to specify a range, only greater than less than or equal to. How does one do this?

Question 2: is there an easier way of doing this other than "IF"?

Thanks in advance for your help
Really looking forward to your answers

David

[Ron Coderre]

Just to a feel for this, put this range of cells in:

column_ A____B
1 0.00000 INVALID
2 2031801 VALID
3 2031836 INVALID
4 2032801 VALID
5 2032803 INVALID
6 2051001 VALID
7 2051397 INVALID
8 2052001 VALID
9 2052060 INVALID

Then put this formula in cellF1:
=VLOOKUP(E1,$B$1:$C$9,2,1)

The forumula will test any number in cell E1 for validity as a student ID.

I hope that gives you something to work with.

Regards,
Ron

[Sivodsi]

Thanks for the quick reply, Ron,
Not quite sure I understand how this would solve the problem.

Thinking about vlookup though, I guess one solution would be to have the entire list of student id numbers in one worksheet, and run a vlookup on the list of id numbers that we get from the students. That way we could see if the number bubbled in exists in the list that we get from administration. Maybe this is what you meant, Ron?

Still, is it possible to do a check using IF to ask if its true if the number is within a range?

eg
ID from exam
2051345 =IF between 2051001 and 2051396, true, if not false, result = TRUE
2151345 =IF between 2051001 and 2051396, true, if not false, result = FALSE

Or something like that?

Or does this ability not exist when using the IF function?

Cheers
David

[Ron Coderre]

Hi, David

The vlookup that I posted checks if the entered student ID number is within certain acceptable ranges. It does that by setting the fourth argument to 1 (true) which engages an "approximate" match. That means 2031800 will fall into the 0 to 2031800 category (INVALID), but 2031802 falls into the 2031801 to 2031835 category (VALID).

It doesn't test if a student ID number that is within the range is actually not a valid student number. For instance, 2051002 maybe within the range for "French majors" but no student may actually have that number.

If you want to test each student ID number to see if it actually exists then, yes, you'd need to put the list somewhere on an Excel sheet and use the Match() function. It will return an error if there is no matching student ID in the list.

Am I helping yet??

Ron