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

  • Thread starter Thread starter Shankley
  • Start date Start date
S

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
 
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
 
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
 
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
 
=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. 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
 
Back
Top