Question about seriessum()

[RK]

I've been trying to get SERIESSUM to work for a long list of automatically
generated coefficients.

=SERIESSUM(1.005,1,1,{1,1,1,1,1,1}) works very well. I can create the array
of ones using VBA too by creating a function that has the number of ones
desired as the input and the array as type variant output.

My question: Is there a worksheet function that can generate an array of
ones. Normally an array of 6 ones is not a problem but I need to generate an
array consisting of anywhere from 200 - 360 ones that can be done on the fly
without storing the ones in a column.

I've scratched my head for a while but seem to find that the VBA method is
the only way this can be done. Any suggestions for a non-VBA or non-column
method?

Thanks,
RK

 

[Ron Coderre]

Would this work for you?:

=COUNT(--(ROW(1:360)>0))

Note: commit that array formula by pressing [Ctrl]+[Shift]+[Enter]

It seemed to work in your example formula:
=SERIESSUM(1.005,1,1,COUNT(--(ROW(1:360)>0)))

Again: commit that array formula by pressing [Ctrl]+[Shift]+[Enter]

Regards,
Ron

 

[Dana DeLouis]

Hi. If you have an array of constant coefficients, would you prefer to use
an equation instead?

r = 1.005
n = 6

?(r*(r^n-1))/(r-1)
6.1058793881469

n=360
?(r*(r^n-1))/(r-1)
1009.53761766488

I hope I got this correct.... :>)

 

[RK]

It seems to give me a different answer if I use r = 1.003 and n = 8 .

=(1.003*(1.003^8-1))/(1.003-1) gives 8.108759.

=SERIESSUM(1.003,0,1,{1,1,1,1,1,1,1,1}) gives 8.084506.

Hopefully I am not doing something wrong either! A series expansion of the
form x^0 + x^1 + x^2 is divergent when x > 1. So I'm not sure if there is a
formula there, especially if the first index power is different from 0 or 1.

Thanks,
RK

 

[RK]

I get a different answer from SERIESSUM when I use your formula.

=SERIESSUM(1.003,0,1,COUNT(--(ROW(1:8)>0))) gives 8 when entered as an array
formula and 1 when entered as a regular formula.

=SERIESSUM(1.003,0,1,{1,1,1,1,1,1,1,1}) gives 8.084506.

I noticed that COUNT(--(ROW(1:8)>0)) gives {1;1;1;1;1;1;1;1} whereas Excel's
arrays are typically {1,1,1,1,1,1,1,1}. In my experience, semicolons are
used to break for rows and commas are used when entering numbers across a
row.

Thanks,
RK

 

[Dana DeLouis]

Hi. The difference is that in your original formula, the second argument to
SeriesSum was 1. You changed it to 0 in this new equation.

=SERIESSUM(1.005,1,1,{1,1,1,1,1,1}) vs
=SERIESSUM(1.003,0,1,{1,1,1,1,1,1,1,1})

If you would like to change the second argument of the SeriesSum formula to
0, then perhaps the following...

n=8
r=1.003

?(r^n - 1)/(r - 1)
8.08450589454283

HTH. :>)

 

[Ron Coderre]

I'm sorry, my mistake. I copied the wrong version of my experiments into the
post.

This works MUCH better:
=SERIESSUM(1.003,0,1,--(ROW(1:8)>0))

Note: commit that array formula by pressing [Ctrl]+[Shift]+[Enter]

It results in 8.08450589454281

Regards,
Ron

 

[Biff]

Just an idea and it does work......and adds some flexibility.....

Create a column full of 1's. Say column I.

Enter the size of the array that you want to use in the calculation in a
cell, say, A1

=SERIESSUM(1.003,0,1,OFFSET(I1,,,A1))

If you want the array to contain 8 values (1's), enter 8 in cell A1.

Biff

 

[Biff]

non-column method?

OH!

Hmmm....

This appears to work: (no column of 1's needed)

=SERIESSUM(1.003,0,1,INDEX((ROW(INDIRECT("1:"&A1))>0)*1,,1))

Again, enter the size of the array desired in cell A1.

Biff

 

[Dana DeLouis]

=SERIESSUM(1.003,0,1,{1,1,1,1,1,1,1,1}) gives 8.084506.

=FV(0.3%,8,-1)

also returns 8.084506

HTH. :>)

 

[RK]

Thanks! I knew that this group would nail it!

RK