MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Calculating Sum Series

• Follow

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

 0
Reply Utf 4/21/2010 6:51:01 AM

"deluth" <deluth@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*(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

 0
Reply Utf 4/21/2010 7:03:01 AM

1 Replies
630 Views

(page loaded in 0.038 seconds)

Similiar Articles:

7/21/2012 11:28:49 PM