How calculate the sum of several functions in vba

[BRUCEWAYNE]

HI5 folks, I'd like to know how to calculate the sum of one function that has several values from 1,2,3 .... to n

the function is

z = (((1 + w) ^ 2))l = (((p) ^ (n - 1)))

y = ((z * l)) ^ ( / 2)

I'd like to know how to calculate the sum ∑ of y, for example from 1,2,3 .... to 10

when w=0,5 p= 0,993449, n= 10 doing the harder way line per line should return
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]199,0484.[/TD]
[/TR]
</tbody>[/TABLE]

How do it by writting in vba code the sum ∑ of y in order to get the results in a faster way ?

Please can someone help me with this problem ? many thanks

 

[lrobbo314]

What is the value of the 'L' variable?

 

[BRUCEWAYNE]

the function is

z = (((1 + w) ^ 2))



l = (((p) ^ (n - 1)))



y = ((z * l)) ^ ( / 2)


the 'L' variable is l = (((p) ^ (n - 1)))

I'd like to know how to calculate the sum ∑ of y, for example from 1,2,3 .... to 10

when w=0,5 p= 0,993449, n= 10 doing the harder way line per line should return
[TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]199,0484.[/TD]
[/TR]
</tbody>[/TABLE]


How do it by writting in vba code the sum ∑ of y in order to get the results in a faster way ?

many thanks in advance

 

[Marcelo Branco]

Maybe I did not understand exactly what you need - I found a different result for n = 10, that is: 138,791

Function GetSum(w As Double, p As Double, k As Long)
    Dim Z As Double, y As Double, l As Double, n As Long
    Dim dbSum As Double
    
    Z = (((1 + w) ^ 2))
    For n = 1 To k
        l = (((p) ^ (n - 1)))
        y = ((Z * l)) ^ ((n) / 2)
        'Just to check...
        Debug.Print "y(" & n & ") = " & y
        dbSum = dbSum + y
    Next n
    GetSum = dbSum
End Function

Debug.Print produced these results
[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"]y(1)[/TD]
[TD="width: 64"]=[/TD]
[TD="width: 64, align: right"]1,5[/TD]
[/TR]
[TR]
[TD]y(2)[/TD]
[TD]=[/TD]
[TD="align: right"]2,23526[/TD]
[/TR]
[TR]
[TD]y(3)[/TD]
[TD]=[/TD]
[TD="align: right"]3,309105[/TD]
[/TR]
[TR]
[TD]y(4)[/TD]
[TD]=[/TD]
[TD="align: right"]4,866744[/TD]
[/TR]
[TR]
[TD]y(5)[/TD]
[TD]=[/TD]
[TD="align: right"]7,110695[/TD]
[/TR]
[TR]
[TD]y(6)[/TD]
[TD]=[/TD]
[TD="align: right"]10,32122[/TD]
[/TR]
[TR]
[TD]y(7)[/TD]
[TD]=[/TD]
[TD="align: right"]14,88319[/TD]
[/TR]
[TR]
[TD]y(8)[/TD]
[TD]=[/TD]
[TD="align: right"]21,32093[/TD]
[/TR]
[TR]
[TD]y(9)[/TD]
[TD]=[/TD]
[TD="align: right"]30,34325[/TD]
[/TR]
[TR]
[TD]y(10)[/TD]
[TD]=[/TD]
[TD="align: right"]42,90062[/TD]
[/TR]
</tbody>[/TABLE]

Formula (; as argument separator in my Excel version)
=getsum(0,5;0,993449;10)

M.

 

[Marcelo Branco]

I think you meant n = 1, 2,....11
Result = 199,0484

M.

 

[BRUCEWAYNE]

ok. yeah it's a mistake that i made here , when put 11 i gave Result = 199,0484.

I´ve got a doubt : How Can i see these written results (one by one), (such as in the column B) produced by Debug.Print from n = 1, 2,....11? How do you that?

I would appreciate to hear from you

 

[Marcelo Branco]

Alt+F11 to open VBEditor

Menu
View > Immediate window

or Ctrl+G

Debug.Print in my function displays y(1), y(2)....y in the Immediate Window

M.

 

[Marcelo Branco]

If you do want to see the results in a worksheet execute the sub Main that calls the sub sGetSum

Sub Main()
    Dim w As Double, p As Double, k As Long
    
    w = 0.5
    p = 0.993449
    k = 10
    sGetSum w, p, k
End Sub

Sub sGetSum(w As Double, p As Double, k As Long)
    Dim Z As Double, y As Double, l As Double, n As Long
    Dim dbSum As Double, dbResults() As Double
    
    ReDim dbResults(1 To k, 1 To 2)
    
    Z = (((1 + w) ^ 2))
    
    For n = 1 To k
        l = (((p) ^ (n - 1)))
        y = ((Z * l)) ^ ((n) / 2)
        dbResults(n, 1) = n
        dbResults(n, 2) = y
        dbSum = dbSum + y
    Next n
    
    'Display results
    Columns("B:C").ClearContents
    Range("B1:C1") = Array("y", "Value")
    Range("B2").Resize(k, 2) = dbResults
    Range("B2").Offset(k) = "Result"
    Range("C2").Offset(k) = dbSum
End Sub

M.

 

[BRUCEWAYNE]

Debug.Print produced these results that you mentioned above:
[TABLE="class: cms_table, width: 192"]
<tbody>[TR]
[TD="width: 64"]y(1)[/TD]
[TD="width: 64"]=[/TD]
[TD="width: 64, align: right"]1,5[/TD]
[/TR]
[TR]
[TD]y(2)[/TD]
[TD]=[/TD]
[TD="align: right"]2,23526[/TD]
[/TR]
[TR]
[TD]y(3)[/TD]
[TD]=[/TD]
[TD="align: right"]3,309105[/TD]
[/TR]
[TR]
[TD]y(4)[/TD]
[TD]=[/TD]
[TD="align: right"]4,866744[/TD]
[/TR]
[TR]
[TD]y(5)[/TD]
[TD]=[/TD]
[TD="align: right"]7,110695[/TD]
[/TR]
[TR]
[TD]y(6)[/TD]
[TD]=[/TD]
[TD="align: right"]10,32122[/TD]
[/TR]
[TR]
[TD]y(7)[/TD]
[TD]=[/TD]
[TD="align: right"]14,88319[/TD]
[/TR]
[TR]
[TD]y(8)[/TD]
[TD]=[/TD]
[TD="align: right"]21,32093[/TD]
[/TR]
[TR]
[TD]y(9)[/TD]
[TD]=[/TD]
[TD="align: right"]30,34325[/TD]
[/TR]
[TR]
[TD]y(10)[/TD]
[TD]=[/TD]
[TD="align: right"]42,90062
[/TD]
[/TR]
</tbody>[/TABLE]
PRODUCT =
[TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: right"]1636964555[/TD]
[/TR]
</tbody>[/TABLE]

How can i find in vba the product Π of y(x)= y(1)* y(2)* y(3)* y(4)* y(5)* y(6)* y(7)* y(8)* y(9)* y(10)???****** id="cke_pastebin" style="position: absolute; top: 343px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: right"]1636964555[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[Marcelo Branco]

Try

Function GetProd(w As Double, p As Double, k As Long)
    Dim Z As Double, y As Double, l As Double, n As Long
    Dim dbProd As Double
    
    Z = (((1 + w) ^ 2))
    dbProd = 1
    For n = 1 To k
        l = (((p) ^ (n - 1)))
        y = ((Z * l)) ^ ((n) / 2)
        'Just to check...
        Debug.Print "y(" & n & ") = " & y
        dbProd = dbProd * y
    Next n
    GetProd = dbProd
End Function

Excel

Formula (; as argument separator in my excel version)
=GetProd(0,5;0,993449;10)

M.