Converting ranges to a single score

**BillLearnLa** · 09-17-2010, 04:03 PM

Hello,
Here's my problem. I have a list of numbers like this:

101
233
411
290
ect.

and want to convert it so everything from 100-150 becomes "1," 151-200 becomes "2," and so on. What is a simple command/formula to do this? Thank you tremendously for your help!!

**shg** · 09-17-2010, 04:06 PM

everything from 100-150 becomes "1," 151-200 becomes "2," and so on.

There is no "so on"; 1 covers a range of 50, 2 covers a range of 49.

**zbor** · 09-17-2010, 04:08 PM

Try this: =MAX(1,INT((A1-1)/50-1))

**BillLearnLa** · 09-17-2010, 04:21 PM

Originally Posted by shg

There is no "so on"; 1 covers a range of 50, 2 covers a range of 49.

I am converting kids' scores from a standardized test into intervals that denote anywhere from below average to advanced proficiency. The ranges are not all equal (that is, the range that from the lowest scoring "advanced" kid to the highest scoring "advanced" kid is smaller than the lowest scoring "basic" kid to the highest scoring "basic"). This is what I meant by "so on," I'm sorry this was difficult to understand for you, hopefully you can wrap your head around the fact that not all ranges are equal for my means.

10 points for your attempted nitpicking though.

**BillLearnLa** · 09-17-2010, 04:27 PM

Originally Posted by zbor

Try this: =MAX(1,INT((A1-1)/50-1))

This was just an example of my scores and ranges, it's actually more like:

100-139
140-210
211-280

There are about 15 ranges and these aren't even accurate for my needs, but you get the idea. Not all ranges are equal, I know there's some way to include the ranges in a formula and then then make the output equal a specific number without the use of quotients and products. I really do appreciate the help though.

**zbor** · 09-17-2010, 04:29 PM

If they are not equal can you please write all of them (exactly)

**shg** · 09-17-2010, 04:29 PM

=match(a1, {100,140,211})

**daddylonglegs** · 09-17-2010, 05:40 PM

Probably the most robust approach is to create a table containing the lower bound of each range, say in A2:A16, so in your example A2 will be 100, A3 will be 140, A4 will be 211 etc.

Then in the adjacent column, B2:B16, list the values you want returned for each range, e.g. B2 = 1, B3 = 2 etc.

Now you can name the table A2:B16 MyTable and use formula

=LOOKUP(A1,MyTable)