Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have the following function:

f(n) = f(n - 1) + (n - 1)
f(0) = 0
n >= 0

I have n declared on column A, and need the result of f(n) on column B.

I'm trying to find the Excel formula equivalent for this function.

Sample Result:

A | B
--+--
0 | 0

or:

A | B
--+--
1 | 0

or:

A | B
--+--
4 | 6

but never:

A | B
--+--
0 | 0
1 | 0
2 | 1
...

The biggest problem is, I can't simulate the value of f(n - 1). So referencing the previous row like the above example is invalid. I'm almost sure the answer is trivial, I just can't find it.

share|improve this question
add comment

migrated from stackoverflow.com Apr 10 '10 at 13:34

This question came from our site for professional and enthusiast programmers.

8 Answers

up vote 12 down vote accepted

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
...
share|improve this answer
    
presumably f is just a stand in for a more complex recusive function –  Scott Weinstein Aug 3 '09 at 23:22
    
@Scott Weinstein The question is the question. If am going to get downvoted for answering the question as it was posted, so be it. –  Sinan Ünür Aug 3 '09 at 23:26
    
assuming that column A is going to be sequential then your example works. –  Stan R. Aug 3 '09 at 23:29
    
Scott, the question doesn't imply that at all. If the question is intended to mean, "How can I perform a general recursive function in Excel?" than, well, that's what the question should say. The less snarky version of Sinan's (completely correct) answer is: f(n + 1) = (n^2 + n)/2 –  WCWedin Aug 3 '09 at 23:31
    
good points, perhaps less presumption on my part... –  Scott Weinstein Aug 3 '09 at 23:48
show 6 more comments

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.

share|improve this answer
add comment

Not sure how to do it with pure formulas. One option is a UDF

Public Function f(n As Integer) As Integer
    If (n = 0) Then
        f = 0
        Exit Function
    End If

    If (n > 0) Then
        f = f(n - 1) + (n - 1)
    End If
End Function

and then the formula is just =f(A1)

share|improve this answer
    
beat me with the code :P –  Stan R. Aug 3 '09 at 23:31
add comment

The function can be restated to eliminate the recursion.

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

f(4)=1+2+3=6
f(5)=1+2+3+4=10
f(6)=1+2+3+4+5=15

There's a pattern here:

f(4)=1+2+3=6=4*1.5
f(5)=1+2+3+4=10=5*2
f(6)=1+2+3+4+5=15=6*2.5

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.

share|improve this answer
add comment

Do you need to solve it recursively? That's certainly not the nicest way to solve it:

Sum the numbers 1 to 10

   1 + 2 + 3 + 4 + 5
+ 10 + 9 + 8 + 7 + 6
  --  --  --  --  --
  11 +11 +11 +11 +11 = 55

or, as it's summarised, (n+1)(n/2) -- with n=10, this is obviously 11 x 5

share|improve this answer
    
Doh! Forgot about Gauss... –  Greg Aug 3 '09 at 23:33
    
I should point out that I'm actually solving for (n+1) as it's phrased in the question, but the normal expression of this problem is summing to a number, inclusive –  Gareth Aug 3 '09 at 23:37
add comment

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
    Else
        f = f(n - 1) + (n - 1)
    End If
End Function

Now you can call this directly:

=f(A2)
share|improve this answer
add comment
=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.

share|improve this answer
add comment

Excel has something called Array Formulas, which allows a function to return a set of values. You can read more about them here. These will effectively let you write, with some creativity, analogs to recursive algorithms. Array formulas must be entered in using Shift-Ctrl-Enter.

To answer your question, have an array function return 1 through n-1, and wrap it in a sum. Here, Indirect creates a reference, and A1 holds n:

=SUM( ROW( INDIRECT("1:"&A1-1) ) )
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.