| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
<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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
<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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
<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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
<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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
<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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
<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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
<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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
<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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
<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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
<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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
| VBA function for "Mean" using Array as argument | Excel Worksheet Functions | |||
| clock | Excel Worksheet Functions | |||
| Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions | |||
| Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Linear Mode
