<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

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

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

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"Shankley" <[email protected]> wrote in message
news:[email protected]...
> 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

One way where A1 holds n

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


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Shankley" <[email protected]> wrote in message
news:[email protected]...
> 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)))

"Peo Sjoblom" wrote:

> One way where A1 holds n
>
> =SUMPRODUCT(ROW(INDIRECT("1:" & A1))^2)
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
>
> "Shankley" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

On Mon, 8 May 2006 19:11:02 -0700, JMB <[email protected]>
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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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

On Mon, 8 May 2006 18:49:10 -0400, "Dana DeLouis"
<[email protected]> 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

On Mon, 8 May 2006 16:01:29 -0700, "Peo Sjoblom"
<peo.sjoblom@nw^^excelsolutions.com> 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

>>=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" <[email protected]> wrote in message
news:[email protected]...
> On Mon, 8 May 2006 19:11:02 -0700, JMB <[email protected]>
> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > 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

On Tue, 9 May 2006 03:18:46 -0400, "Dana DeLouis"
<[email protected]> 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

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"
> <[email protected]> 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
>