|
<REQ Can anyone help me build a function, please read....
Basically, I need to do a Sum of the Squares of the first 'n' Natural
Numbers. Something like: function_name(n) If 'n' is 8 say, then the function would return 204. Thats 1*1 + 2*2 + 3*3 + 4*4 + 5*5 + 6*6 + 7*7 + 8*8 Likewise if 'n' is 6 say, then the function would return 91! Or, if no funtion possible, is it possible to do the calculation with conventional methods? Thanks, Shankley - FunaxyrlRap-nocnglnubbqbgpbz |
<REQ Can anyone help me build a function, please read....
Hi. If your number is in A1, then perhaps...
=A1*(1 + A1)*(1 + 2*A1)/6 -- HTH. :) Dana DeLouis Windows XP, Office 2003 "Shankley" wrote in message ... Basically, I need to do a Sum of the Squares of the first 'n' Natural Numbers. Something like: function_name(n) If 'n' is 8 say, then the function would return 204. Thats 1*1 + 2*2 + 3*3 + 4*4 + 5*5 + 6*6 + 7*7 + 8*8 Likewise if 'n' is 6 say, then the function would return 91! Or, if no funtion possible, is it possible to do the calculation with conventional methods? Thanks, Shankley - FunaxyrlRap-nocnglnubbqbgpbz |
<REQ Can anyone help me build a function, please read....
One way where A1 holds n
=SUMPRODUCT(ROW(INDIRECT("1:" & A1))^2) -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Shankley" wrote in message ... Basically, I need to do a Sum of the Squares of the first 'n' Natural Numbers. Something like: function_name(n) If 'n' is 8 say, then the function would return 204. Thats 1*1 + 2*2 + 3*3 + 4*4 + 5*5 + 6*6 + 7*7 + 8*8 Likewise if 'n' is 6 say, then the function would return 91! Or, if no funtion possible, is it possible to do the calculation with conventional methods? Thanks, Shankley - FunaxyrlRap-nocnglnubbqbgpbz |
<REQ Can anyone help me build a function, please read....
A small variation, array entered
=SUMSQ(ROW(INDIRECT("1:"&A1))) "Peo Sjoblom" wrote: One way where A1 holds n =SUMPRODUCT(ROW(INDIRECT("1:" & A1))^2) -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Shankley" wrote in message ... Basically, I need to do a Sum of the Squares of the first 'n' Natural Numbers. Something like: function_name(n) If 'n' is 8 say, then the function would return 204. Thats 1*1 + 2*2 + 3*3 + 4*4 + 5*5 + 6*6 + 7*7 + 8*8 Likewise if 'n' is 6 say, then the function would return 91! Or, if no funtion possible, is it possible to do the calculation with conventional methods? Thanks, Shankley - FunaxyrlRap-nocnglnubbqbgpbz |
<REQ Can anyone help me build a function, please read....
On Mon, 8 May 2006 19:11:02 -0700, JMB
wrote: A small variation, array entered =SUMSQ(ROW(INDIRECT("1:"&A1))) Thanks JMB, but your version just returns '1' whatever! I'll try and figure out what's wrong, ok. Cheers, Shankley "Peo Sjoblom" wrote: One way where A1 holds n =SUMPRODUCT(ROW(INDIRECT("1:" & A1))^2) -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Shankley" wrote in message ... Basically, I need to do a Sum of the Squares of the first 'n' Natural Numbers. Something like: function_name(n) If 'n' is 8 say, then the function would return 204. Thats 1*1 + 2*2 + 3*3 + 4*4 + 5*5 + 6*6 + 7*7 + 8*8 Likewise if 'n' is 6 say, then the function would return 91! Or, if no funtion possible, is it possible to do the calculation with conventional methods? Thanks, Shankley - FunaxyrlRap-nocnglnubbqbgpbz - FunaxyrlRap-nocnglnubbqbgpbz |
<REQ Can anyone help me build a function, please read....
On Mon, 8 May 2006 18:49:10 -0400, "Dana DeLouis"
wrote: Hi. If your number is in A1, then perhaps... =A1*(1 + A1)*(1 + 2*A1)/6 Thanks very much Dana DeLouis, it worked a treat, and now I'll just try figure out why :) Cheers, Shankley - FunaxyrlRap-nocnglnubbqbgpbz |
<REQ Can anyone help me build a function, please read....
On Mon, 8 May 2006 16:01:29 -0700, "Peo Sjoblom"
wrote: One way where A1 holds n =SUMPRODUCT(ROW(INDIRECT("1:" & A1))^2) Peo Sjoblom, Thanks very much, it works fine, and now I'll just try figure out why :) Cheers, Shankley - FunaxyrlRap-nocnglnubbqbgpbz |
<REQ Can anyone help me build a function, please read....
=SUMSQ(ROW(INDIRECT("1:"&A1)))
I'll try and figure out what's wrong, ok. Hi. JMB's equation worked ok for me. It's an array equation, so enter the equation with Ctrl+Shift+Enter. (Not just Enter). The function "Sumproduct" does not need to be array-entered. Just note that with these functions, 'n is limited to the number of Rows on a sheet. Currently at 65536. With the direct equation, n is limited to 144224. (and 3408917801 using vba) -- HTH. :) Dana DeLouis Windows XP, Office 2003 "Shankley" wrote in message ... On Mon, 8 May 2006 19:11:02 -0700, JMB wrote: A small variation, array entered =SUMSQ(ROW(INDIRECT("1:"&A1))) Thanks JMB, but your version just returns '1' whatever! I'll try and figure out what's wrong, ok. Cheers, Shankley "Peo Sjoblom" wrote: One way where A1 holds n =SUMPRODUCT(ROW(INDIRECT("1:" & A1))^2) -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Shankley" wrote in message ... Basically, I need to do a Sum of the Squares of the first 'n' Natural Numbers. Something like: function_name(n) If 'n' is 8 say, then the function would return 204. Thats 1*1 + 2*2 + 3*3 + 4*4 + 5*5 + 6*6 + 7*7 + 8*8 Likewise if 'n' is 6 say, then the function would return 91! Or, if no funtion possible, is it possible to do the calculation with conventional methods? Thanks, Shankley - FunaxyrlRap-nocnglnubbqbgpbz - FunaxyrlRap-nocnglnubbqbgpbz |
<REQ Can anyone help me build a function, please read....
On Tue, 9 May 2006 03:18:46 -0400, "Dana DeLouis"
wrote: =SUMSQ(ROW(INDIRECT("1:"&A1))) I'll try and figure out what's wrong, ok. Hi. JMB's equation worked ok for me. It's an array equation, so enter the equation with Ctrl+Shift+Enter. (Not just Enter). The function "Sumproduct" does not need to be array-entered. Just note that with these functions, 'n is limited to the number of Rows on a sheet. Currently at 65536. With the direct equation, n is limited to 144224. (and 3408917801 using vba) Hi Dana, thanks for the feedback, I tried the Ctrl+Shift+Enter, and it worked fine! Thanks again to everyone, Shankley - FunaxyrlRap-nocnglnubbqbgpbz |
<REQ Can anyone help me build a function, please read....
More on Sumproduct. Much of its usefulness is not documented in Excel help
file. http://www.xldynamic.com/source/xld.SUMPRODUCT.html "Shankley" wrote: On Tue, 9 May 2006 03:18:46 -0400, "Dana DeLouis" wrote: =SUMSQ(ROW(INDIRECT("1:"&A1))) I'll try and figure out what's wrong, ok. Hi. JMB's equation worked ok for me. It's an array equation, so enter the equation with Ctrl+Shift+Enter. (Not just Enter). The function "Sumproduct" does not need to be array-entered. Just note that with these functions, 'n is limited to the number of Rows on a sheet. Currently at 65536. With the direct equation, n is limited to 144224. (and 3408917801 using vba) Hi Dana, thanks for the feedback, I tried the Ctrl+Shift+Enter, and it worked fine! Thanks again to everyone, Shankley - FunaxyrlRap-nocnglnubbqbgpbz |
| All times are GMT +1. The time now is 02:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com