Sum of a number series

[JimM]

Hi guys

I'm looking for a formula that will sum a sequential number series eg for 1 to 5 it would do 1 + 2 + 3 + 4 + 5 = 15

I know I've seen it on here somewhere but haven't been able to find the answer using search criteria

Anyone able to point me in the right direction

Cheers

Jim

 

[Dave Patton]

D1 has 1
E1 has 5

=SUMPRODUCT(ROW(INDIRECT(D1&":"&E1)))

=E1*(E1+1)/2 I believe credit to C. F. Gauss ~ 1785

 

[hardeep.kanwar]

Hi guys

I'm looking for a formula that will sum a sequential number series eg for 1 to 5 it would do 1 + 2 + 3 + 4 + 5 = 15

I know I've seen it on here somewhere but haven't been able to find the answer using search criteria

Anyone able to point me in the right direction

Cheers

Jim

I think we Need more Details

 

[dms37]

Assuming it's just a sequence from 1 counting up ie. 1, 2, 3,..... If the sequence ends at n (or a number in a cell), the formula is

Total = n*(n + 1)/2

So if you have in A1 the length of the sequence you will need

=A1*(A1+1)/2

 

[West Man]

If the number sequence does not start with one, then the more general formula is(assuming the range is A3:A15):

=(A3+A15)*(A15-A3+1)/2

 

[pgc01]

If the number sequence does not start with one, then the more general formula is(assuming the range is A3:A15):

=(A3+A15)*(A15-A3+1)/2

Hi West Man

If you are thinking about an arithmetic progression, I believe you mean:

=ROWS(A3:A15)*(A3+A15)/2

 

[West Man]

Hi PGC,

I was not thinking as generally as you, as I was looking at sequential numbers where the start value was not one in which A15-A3+1 should be equivalent to ROWS(A3:A15), but your way is slightly cleaner and certainly handles increments other than one.

Clearly superior.

 

[pgc01]

I was looking at sequential numbers where the start value was not one in which A15-A3+1 should be equivalent to ROWS(A3:A15), ...

You are right, if the increment is 1 the 2 formulas are equivalent.

I think, however, that Hardeep is right, we need more information. If the values were in a range we would simply use =SUM(A3:A15). I don't think that's what Jim wants.

 

[West Man]

Ok, so the minumum we must know to get the sum is the starting number, the ending number and the increment - or we must have a way of computing these. Assuming these are in C2:E2 respectively, then a formula would be: =(C1+D1)*((D1-C1)/E1+1)/2

Of course if we knew the number of cells to be summed, we could get by with either the starting or ending number and the increment.