If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating a Series
Please help me put together a function to calculate a sum series as follows:
For n = 0 to n=(B3-B1): sum ((A1 + (A3*n)) * A6) Thank you, Deluth |
#2
|
|||
|
|||
Calculating a Series
"deluth" wrote:
Please help me put together a function to calculate a sum series as follows: For n = 0 to n=(B3-B1): sum ((A1 + (A3*n)) * A6) 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*(B3-B1+1) + A3*(B3-B1)*(B3-B1+1)/2) which can be simplified further to: =A6 * (B3-B1+1) * (A1+A3*(B3-B1)/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 |
Thread Tools | |
Display Modes | |
|
|