Assigning a Number Based on a Range.

[TwinkEles]

OOps. Guess my message got deleted in my last entry! I need to indicate if
a student's score is a level 1, a level 2, or a level 3 based on the number
of answers correct out of 22. Specifically, the scale is: 1-10 equals a
level 1; 11 - 17 equals a level 2; anything over 17 equals a level 3.

I have a lot of students! I have entered in column B the number of items
correct. In column C the total number of items which is 22. In column D I
would like the spreadsheet to automatically calculate the level.

 

[Pete_UK]

Put this in D2:

=IF(B2="","",IF(B2<=10,1,IF(B2<=17,2,3)))

then copy down as required.

Hope this helps.

Pete

 

[Rick Rothstein]

Here is another way to do it...

=IF(B2="","",1+(B2>10)+(B2>17))

Put this in D2 (Row 1 is assumed to be a header row) and copy down as
needed. If the number of students is fixed and you will only put formulas in
Column D in rows with data (that is, you won't copy it down past the end of
the last used row), then you can eliminate the blank cell test and use
this...

=1+(B2>10)+(B2>17)

 

[xlmate]

Hi Rick

Creative solution!!

cheers,

Here is another way to do it...

=IF(B2="","",1+(B2>10)+(B2>17))

Put this in D2 (Row 1 is assumed to be a header row) and copy down as
needed. If the number of students is fixed and you will only put formulas in
Column D in rows with data (that is, you won't copy it down past the end of
the last used row), then you can eliminate the blank cell test and use
this...

=1+(B2>10)+(B2>17)

 

[Ashish Mathur]

Hi,

In a separate range, say E2:G4, type 1,11 and 18 in E2:E4, 10,17 in F2:F4
and Level 1, Level 2 and Level 3 in G2:G4

In cell D1, use the formula =vlookup(D1,E2:G4,3)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com