Formula that increases in value every 50

Question

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.

Answer 1

Please try this =INT((A1+49)/50)

It will return 1, 2, 3, etc, for 1-50, 51-100, 101-150...

Update Yes, you can do this with formulas like

=25*(INT((INT((A1-1)/50)*50+49)/50))*(INT((INT((A1-1)/50)*50+49)/50)+1)+(A1-INT((A1-1)/50)*50)*(INT((A1+49)/50))

or

=25*INT((INT((A1+49)/50)*50+49)/50)*(INT((INT((A1+49)/50)*50+49)/50)+1)-(INT((A1+49)/50)*50-A1)*INT((A1+49)/50)

this will work without auxiliary table

Answer 2

Here’s an answer that’s slightly shorter than the ones in JohnSUN’s answer:

=A1 + FLOOR((A1-1)/50,1)*(MOD(A1-1,50)+1) + 50*(FLOOR((A1-1)/50,1)*(FLOOR((A1-1)/50,1)-1))/2

You say you want to be able to calculate value N without needing to know value N−1 (and all the preceding values).  I can do that, and make the formula shorter, by adding a “helper” column.  If Column Z is not being used for anything, set Z1 to =FLOOR((A1-1)/50,1), and then you can compute the value for any row with

=A1 + Z1*(MOD(A1-1,50)+1) + 50*(Z1*(Z1-1))/2

I can’t explain how it works right now.  I just played around with it for a while and applied some intuition, and I came up with it.

Answer 3

Here is a short formula calculating output without neither helper column, neither relying on previous value:
=LET(groups,INT(([@number]-1)/50),groups*(groups+1)/2*50+(groups+1)*(MOD([@number]-1,50)+1))

The principle of the formula:
50 * n*(n+1)/2 + mod * (n+1)
where n is the number of complete group of 50 numbers int((n-1)/50) and mod is the count of numbers of the last, incomplete group MOD(n-1,50)+1)

calculating for e.g. 256:

• 50 * 1 + 50 * 2 + 50 * 3 + 50 * 4 + 50 * 5 + 6 * 6
• = 50 * (1 + 2 + 3 + 4 + 5) + 6 * 6
• = 50 * ( 5 * 6 / 2) + 6 * 6 (sum of number from 1 to n = n(n+1)/2)*

Answer 4

int i = 175;
int result = i + (i / 50);

result is equal to 178 in this example.

If you were not using an int:

float i = 175;
float result = i + (float)Math.Floor((float)i / 50);

So I guess the formula would be X = A + FLOOR(A / 50)