Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Question about seriessum()
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
=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 |
#11
|
|||
|
|||
Thanks! I knew that this group would nail it!
RK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting Rows w/formulas question | Excel Worksheet Functions | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Question about combining data from multiple workbooks into one rep | Excel Discussion (Misc queries) | |||
Function question | Excel Worksheet Functions | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions |