CODEWITHSUNDEEP
excelvbamath
MoonMoon
MoonMoon

Reputation: 13

New to VBA in Excel. Trying to sum an incremented function

So what I am trying to do is take the difference between two values(x) and (y) to get (n). I then want to run (x) through a formula (n) times incrementing (x) each time. Then I want to output the sum all of those results into a cell. Cant figure out how to do it neatly within one cell like normal, so I've turned to VBA for once.

Here is what I have so far:

Sub Math()
'
'Math
'

Dim i As Integer
i = 0
Do While i < ((E42) - (d42))
cell(h42).Value = ((((d42) + i) ^ 2) * 100) / 3


End Sub

What I'm stuck on is how to get the result of each loop and sum them all together. I expect to have an i value that can range anywhere from 1-100. The only way I can think that would definitely work is messy where i would have a large number of cells in a column set aside that will calculate each of the iterations individually, then sum all of those together.

Alternatively, if theres a way to write a function that can calculate the sum(n) of ((x+n)^2)*100/3 then that would be much nicer but I cant think of how it would be written.

Upvotes: 1

Views: 81

Answers (1)

John Coleman
John Coleman

Reputation: 51998

Here is how you can make a function (which can be used directly in worksheet formulas) to form a sum:

Function eval_sum(n As Long, x As Double) As Double
    Dim s As Double, i As Long
    For i = 0 To n - 1
        s = s + (x + i) ^ 2 
    Next i
    eval_sum = s * 100 / 3
End Function

This function evaluates:

100/3 * (x^2 + (x+1)^2 + (x+2)^2 + ... + (x+(n-1))^2)

It wasn't completely clear if this is what you were trying to do. If not, you can tweak the code to fit your needs.

Small point: I used Long rather than Integer. Forget that Integer exists. It is really legacy from the days of limited memory and 16-bit computers. In modern Excel an Integer is stored with 32 bits anyway. You are just risking overflow for no good reason.

Upvotes: 3

Related Questions