# 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?

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.
– ale
Jan 8, 2013 at 0:39
• Thanks! `vlookup` is tricky stuff, but definitely very powerful. It took a TON of time to get the concepts straight :) Jan 8, 2013 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 !! Jan 7, 2013 at 20:54
• @AlEverett Have I earned an upvote? Jan 7, 2013 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, 2013 at 21:11

A separate table is generally better for things like prices that may need updating but for 'binning' numbers a formula may be more convenient:

`````` =match(A1,{0,6,10,16,22,27,32})
``````

A blank in A1 will also return `1` and anything over 36 will also return `7`.