I have a range of values, 1..1000, and I want to get the sum where values 1 to 50 are valued at 1, while 51-100 are valued at 2, 101-150 valued at 3...
So I would end up with:
number | value |
---|---|
1 | 1 |
2 | 2 |
3 | 3 |
.. | .. |
49 | 49 |
50 | 50 |
51 | 52 |
52 | 54 |
.. | .. |
99 | 148 |
100 | 150 |
101 | 153 |
102 | 156 |
I came up with this formula:
=(FLOOR(A1/50, 1)*50)+(MOD(A1,50)*(FLOOR(A1/50, 1)+1))
And it worked until I got to 100... then it falls apart...
The 2nd column is what the formula produces, but the 3rd is what I'm expecting.