Need excel formula to sum 1+2+3...+N given a number N
 

I am looking for a formula to which I can supply a number N and have it
calculate 1+2+3+4.....+N. I realise that I can enter 1 to N in as many cells
then use SUM but this won't do for what I need to achieve.

As Gauss found out when in elementary school" SUM(1,2, .....N) = N*(N+1)/2
So with N in A1 use = A1*(A1+1)/2

Homework?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"LAdekoya" wrote in message
...
I am looking for a formula to which I can supply a number N and have it
calculate 1+2+3+4.....+N. I realise that I can enter 1 to N in as many
cells
then use SUM but this won't do for what I need to achieve.

If is always 1,2,3....N you can use Gauss's solution

=IF(ISEVEN(A1),(A1/2)*(1+A1),INT(A1/2)*(A1)+A1)

where A1 holds N

otherwise use

=SUMPRODUCT(--(ROW(INDIRECT(A1&":"&A2))))

where A1 and A2 are first and last numbers


--
HTH

Bob Phillips

"LAdekoya" wrote in message
...
I am looking for a formula to which I can supply a number N and have it
calculate 1+2+3+4.....+N. I realise that I can enter 1 to N in as many
cells
then use SUM but this won't do for what I need to achieve.

Thanks Bob/Bernard. It's my fault - I should have explained that my numbers
may also be fractions. In fact they are each the inverse of 1 through to N.
So 1/1 +1/2 + 1/3.......1/N. I assumed the same formula could apply.

Neither of the formulas you provided seem to work in this scenario and I get
a REF error. Can you help further? Not a math or excel bunny I am afraid, so
the more explanation the better.


"Bob Phillips" wrote:

If is always 1,2,3....N you can use Gauss's solution

=IF(ISEVEN(A1),(A1/2)*(1+A1),INT(A1/2)*(A1)+A1)

where A1 holds N

otherwise use

=SUMPRODUCT(--(ROW(INDIRECT(A1&":"&A2))))

where A1 and A2 are first and last numbers


--
HTH

Bob Phillips

"LAdekoya" wrote in message
...
I am looking for a formula to which I can supply a number N and have it
calculate 1+2+3+4.....+N. I realise that I can enter 1 to N in as many
cells
then use SUM but this won't do for what I need to achieve.

=SUMPRODUCT(--(1/ROW(INDIRECT(B1&":"&B2))))

--
HTH

Bob Phillips

"LAdekoya" wrote in message
...
Thanks Bob/Bernard. It's my fault - I should have explained that my
numbers
may also be fractions. In fact they are each the inverse of 1 through to
N.
So 1/1 +1/2 + 1/3.......1/N. I assumed the same formula could apply.

Neither of the formulas you provided seem to work in this scenario and I
get
a REF error. Can you help further? Not a math or excel bunny I am afraid,
so
the more explanation the better.


"Bob Phillips" wrote:

If is always 1,2,3....N you can use Gauss's solution

=IF(ISEVEN(A1),(A1/2)*(1+A1),INT(A1/2)*(A1)+A1)

where A1 holds N

otherwise use

=SUMPRODUCT(--(ROW(INDIRECT(A1&":"&A2))))

where A1 and A2 are first and last numbers


--
HTH

Bob Phillips

"LAdekoya" wrote in message
...
I am looking for a formula to which I can supply a number N and have
it
calculate 1+2+3+4.....+N. I realise that I can enter 1 to N in as many
cells
then use SUM but this won't do for what I need to achieve.

Thanks

Louis

"Bob Phillips" wrote:

=SUMPRODUCT(--(1/ROW(INDIRECT(B1&":"&B2))))

--
HTH

Bob Phillips

"LAdekoya" wrote in message
...
Thanks Bob/Bernard. It's my fault - I should have explained that my
numbers
may also be fractions. In fact they are each the inverse of 1 through to
N.
So 1/1 +1/2 + 1/3.......1/N. I assumed the same formula could apply.

Neither of the formulas you provided seem to work in this scenario and I
get
a REF error. Can you help further? Not a math or excel bunny I am afraid,
so
the more explanation the better.


"Bob Phillips" wrote:

If is always 1,2,3....N you can use Gauss's solution

=IF(ISEVEN(A1),(A1/2)*(1+A1),INT(A1/2)*(A1)+A1)

where A1 holds N

otherwise use

=SUMPRODUCT(--(ROW(INDIRECT(A1&":"&A2))))

where A1 and A2 are first and last numbers


--
HTH

Bob Phillips

"LAdekoya" wrote in message
...
I am looking for a formula to which I can supply a number N and have
it
calculate 1+2+3+4.....+N. I realise that I can enter 1 to N in as many
cells
then use SUM but this won't do for what I need to achieve.

Bob Phillips wrote...
If is always 1,2,3....N you can use Gauss's solution

=IF(ISEVEN(A1),(A1/2)*(1+A1),INT(A1/2)*(A1)+A1)

where A1 holds N

Gauss's formula was simpler that Phillips's formula. No need to check
whether A1 is even because either A1 or A1+1 is even. So

=A1*(A1+1)/2

otherwise use

=SUMPRODUCT(--(ROW(INDIRECT(A1&":"&A2))))

where A1 and A2 are first and last numbers
....

Or

=(A2*(A2+1)-(A1-1)*A1)/2

"Harlan Grove" wrote in message
ups.com...
Gauss's formula was simpler that Phillips's formula.

LOL. I know it is. I knew the formula, just made a hash of it as I forgot
it was as true for an odd final n umber as it was for an even.

Or

=(A2*(A2+1)-(A1-1)*A1)/2


Yeah, that's nicer.