<REQ> Can anyone help me build a function, please read....

[Shankley]

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

 

[Dana DeLouis]

Hi. If your number is in A1, then perhaps...

=A1*(1 + A1)*(1 + 2*A1)/6

 

[Peo Sjoblom]

One way where A1 holds n

=SUMPRODUCT(ROW(INDIRECT("1:" & A1))^2)


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Guest]

A small variation, array entered

=SUMSQ(ROW(INDIRECT("1:"&A1)))

 

[Shankley]

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
-
FunaxyrlRap-nocnglnubbqbgpbz

 

[Shankley]

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

 

[Shankley]

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

 

[Dana DeLouis]

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

 

[Shankley]

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

 

[Guest]

More on Sumproduct. Much of its usefulness is not documented in Excel help
file.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html