Create a google sheet SEQUENCE with given sum of numbers-CodePudding

Need help:

I have a number (let's say 550) and need to create a perfect distributed SEQUENCE of length N where SUM of numbers will give this number (550).

Something like: 10, 20, 30, 40, 50, 60, 70, 80, 90, 100

The only input I have is the length of sequence and the final sum of it's numbers.

Thank you.

CodePudding user response：

if your issue is to divide value x into equal chunks y try:

=INDEX(SEQUENCE(B1, 1)*A1/B1)

or starting from 0:

=INDEX(SEQUENCE(B1, 1, 0)*A1/(B1-1))

CodePudding user response：

So here are some possible starting values (a) and increments (d) that satisfy the conditions where the number of terms n is 10 and the sum S is 550:

Using the formula

=MAKEARRAY(S/n,2,LAMBDA(r,c,if(c=1,r,2/(n-1)*(S/n-r))))

The formula is obtained by re-arranging the

Here are the integer pairs using

=lambda(pairs,filter(pairs,index(pairs,,2)=int(index(pairs,,2))))(MAKEARRAY(S/n,2,LAMBDA(r,c,if(c=1,r,2/(n-1)*(S/n-r)))))

Here are the actual series using

=lambda(intpairs,makearray(rows(intpairs),n,lambda(r,c,index(intpairs,r,1) (c-1)*index(intpairs,r,2))))(lambda(pairs,filter(pairs,index(pairs,,2)=int(index(pairs,,2))))(MAKEARRAY(S/n,2,LAMBDA(r,c,if(c=1,r,2/(n-1)*(S/n-r))))))

It can be verified that each series totals to 550.

Once you have the values of a and d, you can also use Sequence to generate the results:

=sequence(1,n,F10,G10)