# How do I “bin” results in Google Sheets?

I am trying to bin my results so my groupings have lower granularity - going from a 1-10 scale to a 1-5 scale.
For example, any result 1 or 2 would be renamed to 1 in the next column, any result 3 or 4 would be renamed to 2 in the next column, ...

I have tried:

``````=lookup(F3{=10,=9,=8,=7,=6,=5,=4,=3,=2,=1},G3{5,5,4,4,3,3,2,2,1,1})
``````

and get a formula parse error.

Can you help?

• How about just dividing and rounding? – user55949 Aug 27 at 7:43

I think `LOOKUP` is maybe overkill to do this ?

``````=ROUND(range/2,0)
``````

Example 1

``````Entry      Result
10    |    5
9     |    5
8     |    4
7     |    4
6     |    3
5     |    3
4     |    2
3     |    2
2     |    1
1     |    1
``````

Formula used in `B1` : `=ARRAYFORMULA(ROUND(A1:A10/2,0))`

Edit : if you want to use `LOOKUP`, try to sort in ascending order the first range.
Example 2

``````Entry      Result
1     |    1
2     |    1
3     |    2
4     |    2
5     |    3
6     |    3
7     |    4
8     |    4
9     |    5
10    |    5
``````

Tests :
`=LOOKUP(5, \$A\$1:\$A\$10, \$B\$1:\$B\$10)` | Result is `3`
`=LOOKUP(9, \$A\$1:\$A\$10, \$B\$1:\$B\$10)` | Result is `9`
`=LOOKUP(3, \$A\$1:\$A\$10, \$B\$1:\$B\$10)` | Result is `2`

`=LOOKUP("string", \$A\$1:\$A\$10, \$B\$1:\$B\$10)` | Error Did not find value 'string' in `LOOKUP` evaluation.

you can do:

``````=ARRAYFORMULA(IFERROR(VLOOKUP(A1:A,
{1, 1;
3, 2;
5, 3;
7, 4;
9, 5}, 2)))`````` 