Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from superuser.com Jan 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)

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).

share|improve this answer
    
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
add comment

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))))))
share|improve this answer
add comment

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.

share|improve this answer
    
@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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.