• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

2 Way Reverse Lookup???

P

pop

New Member
I am trying to perform a grade referencing task and not sure if i need to perform a reverse lookup on a grade score table. I have a test score and student class grade.

I am struggling with a formula to check the students test grade is associated with their grade level either higher or at their grade level or lower for example

a 3rd Grade student who has a test score of 201 (within than their grade range) would achieve a 3rd Grade rating

a 3rd Grade student who has a test score of 173 (lower than their grade range) would achieve a 2nd Grade rating but if lower still could extend to 1st or kindergarten grades

a 3rd Grade student who has a test score of 214 (higher than their grade range) would achieve a 4th Grade rating but if higher could extend to 5th 6th 7th 8th 9th 10th 11th Grade

and so on

Many thanks in advance
 

Attachments

  • SampleGradeRef.xlsx
    11.6 KB · Views: 14
Pop,

What is the logic that determines when a person jumps to a higher or lower rating ?
 
Hui said:
Pop,

What is the logic that determines when a person jumps to a higher or lower rating ?
Click to expand...
Hi Hui,

the rating will be determined by the test score. Each student will be taking a grade specific tests but if their test results is higher or lower than the scores predetermined for their grade they will be graded according to the next grade range if the test score is higher or lower.

I hope that makes sense.?

So its really whether the test score being input and the grade of the student being input is within the prescribed ranges associated with that grade - if not then to check it its higher or lower and then return the most appropriate grade according to the test score.
 
pop said:
Hi Hui,

the rating will be determined by the test score. Each student will be taking a grade specific tests but if their test results is higher or lower than the scores predetermined for their grade they will be graded according to the next grade range if the test score is higher or lower.

I hope that makes sense.?

So its really whether the test score being input and the grade of the student being input is within the prescribed ranges associated with that grade - if not then to check it its higher or lower and then return the most appropriate grade according to the test score.
Click to expand...
Hi,

I'm not sure I fully understand that because I don't understand the significance of the 7 rows of lookup data, how do we know which row to use or do we always use the row you highlighted yellow?

I have assumed we always use the yellow row and any score in the range 164 - 179; for example, would be 2nd Grade. This is an ARRAY formula so it can't go in the merged cell ( I hate merged cells) where you seem to want the answer. See below for how to enter an ARRAY formula.

=IFERROR(INDEX(B1:M1,MATCH(TRUE,B5:M5>=L14,0)),M1)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.
 
Hi Narayan & Mike

Thanks for the feedback. The yellow rows were just for illustration.

I have looked at the file Narayan sent and am till trying to understand how the array formula is written and the logical behind it..
The formula seems to be working for test scores or marks that are lower and within than those in the specified grade range.
For example if a 7th Grade student scores 254 (when the top mark for the 7th grade is 253), the next grade reference will be 8th Grade - which has a range of 204-259, if the score exceed that range then it should check for the next range etc). The formula doesn't appear to be producing this result on the test scores that exceed the top marks for any of the grande ranges.

Is there any way of making it all one formula so that I can use it to perform a look up function automatically in a table of test scores. I have attached an example in the spreadsheet.
 

Attachments

  • SampleGradeRef.xlsx
    22.1 KB · Views: 3
You must log in or register to reply here.
Back
Top