2

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

actual results of the above formula, compared with desired values

The 2nd column is what the formula produces, but the 3rd is what I'm expecting.

4 Answers 4

7

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

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

Result

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

6
  • Thanks, that gives the incremental value within those ranges but not the sum (your 3rd column) which is what I'm trying to figure out.
    – Phill
    Aug 13 at 12:20
  • @Phill You simply add the increment of the current number to your previous result. e.g. ((51 + 49) / 50) = 2 => 50 + 2 = 52 ; ((52 +49) / 50) = 2 => 52 + 2 = 54 ; ((53 +49) / 50) = 2 => 54 + 2 = 56 Aug 13 at 20:04
  • Ah, I see, I was trying to do it without having to calculate the previous value, but not to worry it works great. Much simplier than my original method. Thanks.
    – Phill
    Aug 14 at 4:43
  • @Phill Please mark the question as solved. Aug 14 at 15:11
  • @JohnSUN ,,, nice one & is working +10 ☺ Aug 15 at 3:55
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.

1

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

enter image description here

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

2
  • 5
    Even if the mathematical concept is the same, his question is specifically about excel, not C++. Please edit your answer to be on-topic for excel. Aug 13 at 19:50
  • Besides, it’s fairly clear that this is not the result that the OP is looking for. Note that, starting in the 50’s, the desired results start getting much bigger than the input values. There’s no way that the result for 175 would be as low as 178. Aug 15 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.