Home » Questions » Computers [ Ask a new question ]

How can I calculate the sum of all positive integers less than n?

How can I calculate the sum of all positive integers less than n?

I have the following function:

Asked by: Guest | Views: 80
Total answers/comments: 5
Guest [Entry]

"Does this help?

f(n) = sum of all positive integers less than n

It should, especially with the help of some arithmetic.

OK, given that people are now posting answers with user defined functions, here is the answer

f(n) = (n-1)n/2

Update: For those who cannot see that the formula does not use any information from any other rows (see Stan R.'s comment below), I jumbled up the order a little bit:

0 =(A1-1)*A1/2 0
9 =(A2-1)*A2/2 36
2 =(A3-1)*A3/2 1
4 =(A4-1)*A4/2 6
6 =(A5-1)*A5/2 15
5 =(A6-1)*A6/2 10
10 =(A7-1)*A7/2 45
8 =(A8-1)*A8/2 28
3 =(A9-1)*A9/2 3
7 =(A10-1)*A10/2 21
1 =(A11-1)*A11/2 0
Guest [Entry]

"The function can be restated to eliminate the recursion.

Let's take a couple of examples here...


There's a pattern here:


which means we can generalize the function to f(n)=1+2+...+n=n*(n-1)/2 for n>1 and f(n)=0 otherwise.

The resulting Excel formula can then be written as =IF(A5>1;A5*(A5-1)/2);0), assuming A5 contains n.

Obviously, if your formula is more complex than the one you gave, it may become quite a bit harder, and it may be a lot quicker and easier to just write a user defined function like the one suggested by Scott and then use that."
Guest [Entry]

"if understand you correctly then you are trying to create a recursive function and circular references in Excel are not allowed. Your best bet is to create your own worksheet function.

Press Alt+F11 to go into VB then Insert>Module
then follow Scott's answer."
Guest [Entry]

"Typically the way that this is done is to define your own functions. In the VBA editor, insert a new module into your workbook and paste in the following function:

Function f(n As Integer)
If n <= 0 Then
f = 0
f = f(n - 1) + (n - 1)
End If
End Function

Now you can call this directly:

Guest [Entry]

"=IF(MOD(A1,2)=0,(A1-1)*ROUND(A1/2,0), (A1) * ((A1-1)/2))

I don't know, if that is what you are looking for."