Prev: Mandatory/Compulsory
Next: Combo Box
From: deluth on 21 Apr 2010 02:51 Greetings, I can't seem to find a formula to do series of sums. I would like to calculate the following series: For n = 0 to n=B1/B3 sum: (A1+(A2*n))*A3 Thank you for your help. Deluth
From: Joe User on 21 Apr 2010 03:03 "deluth" <deluth(a)discussions.microsoft.com> wrote: > I can't seem to find a formula to do series of sums. > I would like to calculate the following series: > For n = 0 to n=B1/B3 > sum: (A1+(A2*n))*A3 I responded to your posting in the same "discussion group" 11 hours earlier. I wrote.... Well, you could write the following UDF. Note that the variable names a1, a3, etc do not have to match the cell names. I use them just to help you relate to the above formula. You can call the UDF from Excel with any cells; for example: =mysum(A1,A3,A6,B1,B3) However, note that the series sum can be reduced to a single algebraic formula. So instead of the UDF, you can write: =A6* (A1*(B3B1+1) + A3*(B3B1)*(B3B1+1)/2) which can be simplified further to: =A6 * (B3B1+1) * (A1+A3*(B3B1)/2) UDF.... Option Explicit Function mysum(a1 As Double, a3 As Double, _ a6 As Double, b1 As Double, b3 As Double) _ As Double Dim s As Double, n As Double s = 0 For n = 0 To b3  b1 s = s + (a1 + (a3 * n)) * a6 Next mysum = s End Function

Pages: 1 Prev: Mandatory/Compulsory Next: Combo Box 