summa function

Posted by Chris on June 29, 2001 8:02 AM

Is there a summation formula in Excel? Namely, I need the following formula:

Summa (from 0 to n) {Some value*N)

For instance:

Summa (from 0 to 3) {5*N) = 30

The value for "n" is in one cell in my sheet and needs to change.

If this isn't clear, i'm looking for this type of formula:
http://www.math.com/tables/expansion/sum-prop.htm

thanks
chris

Posted by Chris on June 29, 2001 9:06 AM

Just to be clear, I'm not asking about the sum of an actual array of values. In this case, the range will vary based on other variables. The formula varies based on the range. So, it is best described as a sum of a series, with the range of series variable and the values not directly computed and placed in an array.

thanks again
chris

Posted by cpod on June 29, 2001 10:34 AM

Here is a UDF that should help:

Public Function Summa(intFrom As Integer, intTo As Integer) As Integer
Dim intCount As Integer, intSum As Integer
intCount = intFrom
intSum = intFrom
Do
intCount = intCount + 1
intSum = intSum + intCount
Loop Until intCount = intTo
Summa = intSum
End Function

Posted by Chris on June 29, 2001 11:42 AM

thanks!

here's how it finally came out. do you know of any way that Excel won't think it is a macro and thus won't prompt users to accept it?

Public Function Summa(intFrom As Integer, intTo As Integer, numCust As String, custValue As Currency) As Currency
Dim intCount As Integer, intSum As Currency
intCount = intFrom
intSum = intFrom
Do
intCount = intCount + 1
intSum = intSum + (numCust * intCount * custValue / intTo)
Loop Until intCount = intTo
Summa = intSum
End Function

Posted by Cliff on June 29, 2001 3:43 PM

The sum of a series of numbers is equal to the sum of the first term and the last term times the number of terms divided by two.

(n/2)*(a+l)

For example: 1+2+3+4+5 = (5/2)*(1+5) = (2.5)*(6) = 15.