Creating A sumation formula

[Clinton]

Sort of a school project, basically I need to sum the following equation:

k goes from 0 to 100 ( A^( k+1 ) ) * ( B / C ) * ( 1 - ( D / E ) )

where A, B, C, D, E are all cell references that increase by k each time for
the sumation, but there is no way to find a funtion, so I think I have to
make a loop in VBA, but I don't really know how to use my reference cells.
Ultimately I would like to call a function like this: =Sum3up( array1,
array2, array3, integer ) where the 3 arrays are my colums or rows, and my
integer is the value of k.

Not much of a program but if I get pointed in the right way I can figure
most things out, once I get a nudge.

Clinton

 

[Jerry W. Lewis]

If by "...cell references that increase by k each time..." you mean that you
want
(A1^1)*(B1/C1)*(1-(D1/E1))
+(A2^2)*(B2/C2)*(1-(D2/E2))
+(A3^3)*(B3/C3)*(1-(D3/E3))
+...

then you can do it by worksheet function

=SUMPRODUCT((OFFSET(A1,ROW(A1:A101)-1;0)^(ROW(A1:A101)))*(OFFSET(B1;ROW(A1:A101)-1;0)/OFFSET(C1;ROW(A1:A101)-1;0))*(1-(OFFSET(D1;ROW(A1:A101)-1;0)/OFFSET(E1;ROW(A1:A101)-1;0))))

which should be much faster than VBA.

Jerry

 

[Leith Ross]

Hello Clinton,

Here is User Defined Function in VBA. It can be used just like
regular Excel Formula.

It assumes that the index K is the number of entries that will b
successively summed according to your formula. The cells don't need t
be contiguous, but do need to be in the same row.

Add a VBA Module to your project and copy this code into it.

USING THE UDF

=Sum3Up(<cell Arg A>, <cell Arg B>, <cell Arg C>, <cell Arg D>, <cel
Arg E>, Index_K)


Code
-------------------
Public Function Sum3Up(ByRef ArgA As Range, ByRef ArgB As Range, ByRef ArgC As Range, ByRef ArgD As Range, ByRef ArgE As Range, ByVal Index_K As Long) As Double

Application.Volatile

Dim I As Long
Dim Sigma As Double

'Summation Formula: Sigma = ( A^( k+1 ) ) * ( B / C ) * ( 1 - ( D / E ) )
For I = 1 To Index_K
'Avoid division by zero error
If ArgC.Cells(I, 1).Value <> 0 And ArgE.Cells(I, 1).Value <> 0 Then
Sigma = Sigma + (ArgA.Cells(I, 1).Value ^ (I + 1) _
* (ArgB.Cells(I, 1).Value / ArgC.Cells(I, 1).Value) _
* (1 - (ArgD.Cells(I, 1).Value / ArgE.Cells(I, 1).Value)))
End If
Next I

Sum3Up = Sigma

End Function