Sum of Numbers

[Peter100]

Hi

Is there a function to calculate the total of the figures between 1 and a given number ?

ie.
the sum of 1 - 24 = 300
the sum of 1 - 36 = 666

In reality I want to enter 24 in cell A1 and in B1 display the result 300

 

[DominicB]

Good evening Peter100

I don't believe there is, but there's a cheeky little formula that statisticians use to work it out :

((n*(n+1))/2

Where n is the number you want to sum up to:

the sum of 1 - 24 = 300

24*25=600
600/2=300

HTH

DominicB

EDIT : The formula you would want is :
=(A1*(A1+1))/2

 

[Aladin Akyurek]

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

Addendum: Exploiting the mathematical regularity

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

is far superior.

 

[erik.van.geit]

little story for illustration

about 100 years ago, when Albert Einstein was 10-12 years old or less ...

Teacher (who wants to have some free-time to prepare the next lesson): Guys & gals, take a little sheet of paper, write down all numbers from 1 to 100 and calculate the sum
Little Albert, crossing his arms and looking to the sky for 5 seconds, takes his pencil and writes down the result. Ready!
1 + 100 = 101
2 + 99 = 101
3 + 98 = 101
...
50 + 51 = 101

hmm... 50 * 101 = 5050

 

[erik.van.geit]

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

Aladin,
I get #REF
this works for me
=SUMPRODUCT(ROW(1:24))
using INDIRECT in SUMPRODUCT, is it possible ?
do I miss something ?

kind regards,
Erik

 

[Peter100]

Cheers All yep they both work

now I can finish my calculator

 

[Aladin Akyurek]

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

Aladin,
I get #REF

It works for me. Of course, it's inferior to (N*(N+1))/2. Also bounded by 65536...

this works for me
=SUMPRODUCT(ROW(1:24))

Not robust: Insert a row before the formula cell to see...

using INDIRECT in SUMPRODUCT, is it possible ?
do I miss something ?
...

Sure, it's possible.

 

[Aladin Akyurek]

Cheers All yep they both work

now I can finish my calculator

Don't opt for the SumProduct version though...

 

[erik.van.geit]

Thanks Aladin,

found the solution
it was related to an error on my sheet

 

[sweater_vests_rock]

as a side note, this story is actually about Karl Gauss, and I believe it was Grade 2. besides, we all know that Einstein stunk at grade school math

cheers. ben.

little story for illustration

about 100 years ago, when Albert Einstein was 10-12 years old or less ...

Teacher (who wants to have some free-time to prepare the next lesson): Guys & gals, take a little sheet of paper, write down all numbers from 1 to 100 and calculate the sum
Little Albert, crossing his arms and looking to the sky for 5 seconds, takes his pencil and writes down the result. Ready!
1 + 100 = 101
2 + 99 = 101
3 + 98 = 101
...
50 + 51 = 101

hmm... 50 * 101 = 5050