Formula to create an incremental range totalling'x'

[Thistledown]

I wish to create a range of figures that total a predetermined amount, but that increase incrementally and evenly. e.g.

10
20
30
40
50

=150

How do i do this?

[XOR LX]

Hi,

Put your first number in. Go to the Home tab→Fill→Series and choose:

Series in: Columns
Type: Linear
Step Value: 10
Stop Value: 150

Regards

[shg]

For a given sum and number of terms, you can ...

o pick the initial value, in which case the increment between terms is

=2*(sum - n * initialValue) / (n*(n-1))

o pick the increment, in which case the initial value is

=sum/n - (n-1)*increment/2

[Thistledown]

Sorry guys, I cant get either to work, although i think the second suggestion should. Please see attached example. The value i am referencing is the 800,000 in cell A1 - the first value in the range A2:J2 is manually entered as 30,000. I want the rest of the range to be incrementally filled to total 800,000.
Example1.xlsx

[shg]

Please Login or Register  to view this content.

[Ace_XL]

As per shg's formula

In B2

=A2+2*($A$1 - 10*$A$2) / (10*(10-1))

[Thistledown]

Wow! thanks, that's great!

[Marcol]

Maybe another way, see this workbook