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

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

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.... :)
--
Dana DeLouis
Win XP & Office 2003


"RK" wrote in message
...
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

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

"Dana DeLouis" wrote in message
...
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.... :)
--
Dana DeLouis
Win XP & Office 2003


"RK" wrote in message
...
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

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

"Ron Coderre" wrote in message
...
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

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. :)
--
Dana DeLouis
Win XP & Office 2003


"RK" wrote in message
...
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

"Dana DeLouis" wrote in message
...
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.... :)
--
Dana DeLouis
Win XP & Office 2003


"RK" wrote in message
...
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

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

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

"RK" wrote in message
...
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

"Dana DeLouis" wrote in message
...
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.... :)
--
Dana DeLouis
Win XP & Office 2003


"RK" wrote in message
...
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

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

"Biff" wrote in message
...
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

"RK" wrote in message
...
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

"Dana DeLouis" wrote in message
...
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.... :)
--
Dana DeLouis
Win XP & Office 2003


"RK" wrote in message
...
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

=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. :)

--
Dana DeLouis
Win XP & Office 2003


"Dana DeLouis" wrote in message
...
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. :)
--
Dana DeLouis
Win XP & Office 2003


"RK" wrote in message
...
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

"Dana DeLouis" wrote in message
...
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.... :)
--
Dana DeLouis
Win XP & Office 2003


"RK" wrote in message
...
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

Thanks! I knew that this group would nail it!

RK