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:


enter image description here

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 '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:


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:
enter image description here

Screen shot, without formulas shown:
enter image description here

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

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


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy