# In Google Spreadsheets, how can I convert a number according to a range?

I'm working with Google Apps (spreadsheet) and I need to convert results (ranging from 0 to 36) to a maximum of 7 according to the following table.

if score is (0-5) result should be 1
if score is (6-9) result should be 2
if score is (10-15) result should be 3
if score is (16-21) result should be 4
if score is (22-26) result should be 5
if score is (27-31) result should be 6
if score is (32-36) result should be 7

Can anyone give me an example of a function doing this?

-

## migrated from superuser.comJan 7 '13 at 21:16

This question came from our site for computer enthusiasts and power users.

## 3 Answers

Try using a `vlookup`:

``````=vlookup(A1,\$C\$1:\$D\$7,2,True)
``````

This example shows the "values" in column A and the resulting groupings in column B.

What's happening here is that the matching parameter in the `vlookup` has been set to `True`, and this allows for approximate matching. In the case of numbers, it means it will match to any number that is less than or equal to the matching parameter.

How does it do this matching? Well, the matching criteria is listed in column C, and maps to the grouping definition in column D.

To clarify this confusing mess, let's look at the example in row 2. The value entered is 9. The `vlookup` takes 9 and applies it to the match criteria in column C. You'll notice that 9 is > 6 but not equal to 10, so it will fall back to the next previous grouping (in this case 6 matches to 2).

-
I have always had trouble getting my head around `vlookup`. This is helpful. – Al E. Jan 8 '13 at 0:39
Thanks! `vlookup` is tricky stuff, but definitely very powerful. It took a TON of time to get the concepts straight :) – OnenOnlyWalter Jan 8 '13 at 1:01

Not pretty, but to convert cell A1 use:

``````=if(A1<6,1,if(A1<10,2,if(A1<16,3,if(A1<22,4,if(A1<27,5,if(A1<32,6,7))))))
``````
-

I've created a Google Spreadsheet in which I tried to interpret your question. With `FREQUENCY` and plain old values, you can accomplish that.

UPDATE: I've chosen the groups more wisely.
Result is now exactly the same as the previous answer !!
Both scores are 105.

Screen shot, with formulas shown:

Screen shot, without formulas shown:

The file is editable, so you can play around: In Google Documents, how can I convert a number according to a range?

I hope in any case it will be helpful.

-
@AlEverett Thank you very much for this constructive remark !! – Jacob Jan Tuinstra Jan 7 '13 at 20:54
@AlEverett Have I earned an upvote? – Jacob Jan Tuinstra Jan 7 '13 at 21:02
I'm not sure how this answers the question - your ranges don't match what the OP asked, and you're using frequency. While binning and counting might be useful, it's not what the OP actually asked. – ernie Jan 7 '13 at 21:11