Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
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).
vlookup. This is helpful.
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.
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.