Creating A sumation formula

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

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

"Clinton" wrote:

> 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

Hello Clinton,

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

It assumes that the index K is the number of entries that will be successively summed according to your formula. The cells don't need to 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>, <cell Arg E>, Index_K)

Please Login or Register  to view this content.

Sincerely,
Leith Ross