Comparing to Range

[ZombieL]

I’m totally out of it this morning, and trying to look it up really hasn’t worked for me yet; so I have an excel question for you.the answer.

So what I want to do is…

Compare two variables: Last Grade Completed and Educational Functioning level

The Problem

Last Grade completed is a single value (like grade 12) while EFL is a category that I need to transform into a number; however, EFLs do not translate neatly into grades, but rather grade ranges (like 11 through 12 or 1 through 1.9).

Ultimately, I would like Excel to check the Last Grade Completed Column and compare it to my translated Grade Ranges, then give me one of three outputs that correspond to whether the value in Last Grade Completed falls into the grade range, falls below the grade range, or is above the grade range (Expected, Above, and Below).

The later has the problem of getting the right IF statements; however, I am also having a problem with actually getting Excel to recognize that I am inputting a number range into a single cell. Is this even possible?

Any Advice is appreciated.

[AliGW]

If you attach a sample workbook, it will be much easier to advise.

[ZombieL]

Unfortunately, I work for a government agency and the data itself is confidential (even if I do not provide a UID). Plus, it's a huge data set almost at the limits of Excel's capabilities. I considered trying to do this in SPSS, but it seemed like it would be much easier and less time consuming to transform this data in this particular instance.

I will see if I can make something up quickly that mimics what I'm looking at.

[AliGW]

Yes - a mock-up without sensitive data is the way to go.

[ZombieL]

Yes - a mock-up without sensitive data is the way to go.

See attached photos.

Columns
A: Last Grade Completed (numerical value between one and 12)
B: EFL Code (formal federal EFL category a student falls into as measured by a test)
C: EFL Grade Range (the grade range that corresponds to the EFL category)
D: EFL/LG Comp

Column D is where I'd like to record whether or not the numerical value in column A:
0, falls within the range in column C
1, is above the range in column C
-1, is below the range in column C

[AliGW]

If you put the extremes of the grade ranges into separate columns, this would be easy, but in reality, not terribly practical. I think a solution with lookup tables would make more sense.

[hemesh]

may be below in D2

Please Login or Register  to view this content.

and drag down


you need to remove spaces beween > and < signs as website does not allow to post them without spaces sometimes

[AliGW]

Create this lookup table somewhere on the sheet:

ABE Int Low 4 5.9
ABE Int High 6 8.9
ABE Low 9 10.9
ABE High 11 12

Use this formula in D2 and copy down:

=IF(A2>VLOOKUP(B2,$H$1:$J$4,3),1,IF(B2>VLOOKUP(B2,$H$1:$J$4,2),-1,0))

where $H$1:$J$4 is the location of the lookup table.

[hemesh]

@ Ali, grades in column A and grade range in column C are changing constatly