Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating A sumation formula
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating A sumation formula
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 ------------------- Sincerely, Leith Ros -- Leith Ros ----------------------------------------------------------------------- Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846 View this thread: http://www.excelforum.com/showthread.php?threadid=48660 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup multiple lookup values and sumation | Excel Worksheet Functions | |||
shaded cell sumation | Excel Discussion (Misc queries) | |||
Error handling VLOOKUP. #N/A - can it be 0 to allow sumation. | Excel Worksheet Functions | |||
Sumation | Excel Discussion (Misc queries) | |||
How am i able to populate a sumation for multiple sheets | Excel Worksheet Functions |