A quick general question regarding size of s'sheet

[RET79]

Hi.

One of my colleagues at work has this spreadsheet in place to work out an integral (I am not yet sure what he is integrating).

Anyway, he has ended up using 50,000 rows of one sheet to do this. What I suspect he has done is split the integral into small bits and added it all back up at the end to get the answer.

Now, personally, I think this method is bad. When he changes one of the inputs for this integral, the spreadsheet takes a few seconds to update all the 50,000 rows and 20 cols full of forumlas.

As a rule of thumb, could anyone confirm or deny my incling, which is that doing things in this manner on excel is sub optimal to say the least, and that he would be better off doing this task using some VBA instead of 50,000 * 20 cells just to integrate something.

(I am obsessed with speed this weekend which is why I am asking these questions)

RET79

[RET79]

Essentially, what I am getting at with the above posting is:

When does it become suboptimal to be using worksheet functions and columns to calculate stuff vs. VBA?

Let me give you another example.

We had this formula to calculate something called 'annuity factors'. It relied on 4 different inputs - x1, x2, x3, x4 whose ranges were as follows:

x1 = 16 to 65 (whole numbers)
x2 = 0 to 49 (whole numbers)
x3 = 3,6, or 12 only
x4 = 1,4 or 12 only.

Each combination produced 1 annuity factor value. The inputs were put into a lot of equations based on a lot of huge tables, since it was all column based calculations.

I did a macro to generate all possible combinations i.e. 49*49*3*3 = 21,609 values.

It took 9 minutes, so it was generating roughly 2400 values per minute.

The macro was based on 4 nested loops for each input. The annuity factors were put on another sheet using cell.offset functions.

Now, I feel that 9 minutes is a long time to do such a task. It was 9 minutes after defining all variants, disenable screen updating etc.

I am guessing that doing more calcs via VBA rather than on the worksheet for this task will greatly reduce the time.

Perhaps it would be helpful to know what is the MAX values you can loop through in one minute, so that I have an idea of how fast excel can work.

OK, sorry for going on about this so much, thanks for reading.

RET79

[RET79]

OK as an experiment, I ran this macro

Option Explicit

Sub loopspeedtest()
Dim i As Long
For i = 1 To 100000
Application.StatusBar = "Calculating " & i

Next i

End Sub

This was completed in 10 seconds. 100,000 in 10 seconds = 10,000 in 1 seconds.

My other macro does 21,609 in 9 minutes!!

So this is why I am thinking that the macro is too slow!!

RET79

[Jack in the UK]

can i ask, why so much on the speed, does it really matter that much?

[RET79]

I am interested in the most efficient coding, optimising the speed of doing things.

Why? Common sense really.

Consider fast cars. There are many people out there with fast cars but who drive them badly and slowly. If I had a fast car, I would want to drive it fast and get the most out of it. Otherwise, why bother having a fast car?

The same logic applies to what I am doing with excel.

[Jay Petrulis]

Hi,

WARNING: VERY LONG POST!

Rather than getting into the speed aspects, how about discussing the mathematics?

Does your friend have an explicit expression to integrate, or is it just data points?

If it is just the data points, search google.com for "integration trapezoid rule" and look for responses provided by Eero Tibar and Harlan Grove.

If you have the expression to evaluate, search google.com for Subject: Integration and Differentiation, with Jay Petrulis as the author. I have written a first attempt at symbolic math using Excel.

The function integrates the expression using three different rules (Midpt, Trapezium, Simpson's) and also gives rotational volumes.

The differentiation is only first derivative, and uses Richardson's extrapolation.

If you find the thread, Alexander Chachin responded with Gaussian quadrature routines that handle 1 or 2 dimensions. Really impressive.

For Romberg Integration or Cubic Splines, search for those subjects and look for David Braden's posts.

For multiple integration, Monte Carlo simulation is the way to go in Excel, at least until I can get a better grasp of Alexander's work and extend it if I can.

For those who are interested, the functions used are provided below. I still have some work to do to remove singularities within the range chosen. Also, I have better algorithms for the derivatives, but haven't written them yet.

So, my not-yet-ready-for-prime-time functions are offered. I have a sample file to anyone who is interested.

Function Integrate(func As String, var As String, a As Double, b As Double, rule As String) As Double

Dim i As Long, temp As Long
Dim n As Double, delta As Double, CumulativeArea As Double, coordinate As Double
Dim FunctionVal() As Variant

func = Trim(func)
func = Subst(func, var)

If a > b Then
    temp = a
    a = b
    b = temp
End If

n = WorksheetFunction.Max(Int(b - a) * 200, 1000)
delta = (b - a) / n
ReDim FunctionVal(0 To n)

Select Case UCase(var)
    Case Is = "X"
        Select Case UCase(Left(rule, 1))
            Case Is = "X" 'solid of revolution about x-axis
                For i = 0 To n
                    coordinate = a + i * delta
                    FunctionVal(i) = (evalx(func, coordinate) ^ 2) * delta
                    CumulativeArea = CumulativeArea + FunctionVal(i)
                Next i
                Integrate = CumulativeArea * WorksheetFunction.Pi
            
            Case Is = "Y" 'solid of revolution about y-axis
                For i = 0 To n
                    coordinate = a + i * delta
                    FunctionVal(i) = Abs(evalx(func, coordinate) * coordinate * delta)
                    CumulativeArea = CumulativeArea + FunctionVal(i)
                Next i
                Integrate = CumulativeArea * 2 * WorksheetFunction.Pi
            
            Case Is = "S" 'Integration using Simpson's Rule
                For i = 0 To n
                    coordinate = a + i * delta
                    If (i = 0 Or i = n) Then
                        FunctionVal(i) = Abs(evalx(func, coordinate))
                    ElseIf i Mod 2 = 0 Then
                        FunctionVal(i) = 2 * Abs(evalx(func, coordinate))
                    Else
                        FunctionVal(i) = 4 * Abs(evalx(func, coordinate))
                    End If
                    CumulativeArea = CumulativeArea + FunctionVal(i)
                Next i
                Integrate = (delta / 3) * CumulativeArea
                
            Case Is = "T" 'Integration using the Trapezoid Rule
                For i = 0 To n
                    coordinate = a + i * delta
                    If (i = 0 Or i = n) Then
                        FunctionVal(i) = Abs(evalx(func, coordinate))
                    Else
                        FunctionVal(i) = 2 * Abs(evalx(func, coordinate))
                    End If
                    CumulativeArea = CumulativeArea + FunctionVal(i)
                Next i
                Integrate = (delta / 2) * CumulativeArea
            
            Case Is = "M" 'Integration using the Midpoint Rule
                For i = 0 To (n - 1)
                    coordinate = ((a + i * delta) + (a + (i + 1) * delta)) / 2
                    FunctionVal(i) = Abs(evalx(func, coordinate))
                    CumulativeArea = CumulativeArea + FunctionVal(i)
                Next i
                Integrate = delta * CumulativeArea
            Case Else
                Integrate = CVErr(xlErrValue)
        End Select

    Case Is = "Y"
        Select Case UCase(Left(rule, 1))
            Case Is = "X" 'solid of revolution about x-axis
                For i = 0 To n
                    coordinate = a + i * delta
                    FunctionVal(i) = Abs(evaly(func, coordinate) * coordinate * delta)
                    CumulativeArea = CumulativeArea + FunctionVal(i)
                Next i
                Integrate = CumulativeArea * 2 * WorksheetFunction.Pi
            
            Case Is = "Y" 'solid of revolution about y-axis
                For i = 0 To n
                    coordinate = a + i * delta
                    FunctionVal(i) = (evaly(func, coordinate) ^ 2) * delta
                    CumulativeArea = CumulativeArea + FunctionVal(i)
                Next i
                Integrate = CumulativeArea * WorksheetFunction.Pi
            
            Case Is = "S" 'Integration using Simpson's Rule
                For i = 0 To n
                    coordinate = a + i * delta
                    If (i = 0 Or i = n) Then
                        FunctionVal(i) = Abs(evaly(func, coordinate))
                    ElseIf i Mod 2 = 0 Then
                        FunctionVal(i) = 2 * Abs(evaly(func, coordinate))
                    Else
                        FunctionVal(i) = 4 * Abs(evaly(func, coordinate))
                    End If
                    CumulativeArea = CumulativeArea + FunctionVal(i)
                Next i
                Integrate = (delta / 3) * CumulativeArea
                
            Case Is = "T" 'Integration using the Trapezoid Rule
                For i = 0 To n
                    coordinate = a + i * delta
                    If (i = 0 Or i = n) Then
                        FunctionVal(i) = Abs(evaly(func, coordinate))
                    Else
                        FunctionVal(i) = 2 * Abs(evaly(func, coordinate))
                    End If
                    CumulativeArea = CumulativeArea + FunctionVal(i)
                Next i
                Integrate = (delta / 2) * CumulativeArea
            
            Case Is = "M" 'Integration using the Midpoint Rule
                For i = 0 To (n - 1)
                    coordinate = ((a + i * delta) + (a + (i + 1) * delta)) / 2
                    FunctionVal(i) = Abs(evaly(func, coordinate))
                    CumulativeArea = CumulativeArea + FunctionVal(i)
                Next i
                Integrate = delta * CumulativeArea
            Case Else
                Integrate = CVErr(xlErrValue)
        End Select
    Case Else
        
End Select
End Function
Function Derivative(func As String, var As String, a As Double, Optional b As Double) As Double

Const h = 0.001
Dim n1 As Double, n2 As Double
Dim term1 As Double, term2 As Double, term3 As Double, term4 As Double
    
    func = Subst(func, var)
Select Case UCase(var)
    Case Is = "X"
        n1 = (evalx(func, a + (h / 2)) - evalx(func, a - (h / 2))) / h
        n2 = (evalx(func, a + h) - evalx(func, a - h)) / (2 * h)
        Derivative = (4 * n1 - n2) / 3
    Case Is = "Y"
        n1 = (evaly(func, a + (h / 2)) - evaly(func, a - (h / 2))) / h
        n2 = (evaly(func, a + h) - evaly(func, a - h)) / (2 * h)
        Derivative = (4 * n1 - n2) / 3
    Case Is = "XY"
        term1 = evalxy(func, a + h, b + h)
        term2 = evalxy(func, a + h, b - h)
        term3 = evalxy(func, a - h, b + h)
        term4 = evalxy(func, a - h, b - h)
        Derivative = ((term1 - term2) - (term3 - term4)) / (4 * h ^ 2)
    Case Else
        Derivative = CVErr(xlErrValue)
End Select
    
End Function

Function evalx(funct As String, xx As Double) As Double
    x = xx
    evalx = Evaluate(funct)
End Function

Function xval()
    xval = x
End Function

Function evaly(funct As String, yy As Double) As Double
    y = yy
    evaly = Evaluate(funct)
End Function

Function yval()
    yval = y
End Function

Function evalxy(funct As String, xx As Double, yy As Double) As Double
    x = xx
    y = yy
    evalxy = Evaluate(funct)
End Function

Function Subst(func As String, var As String) As String
Select Case UCase(var)
Case "X"
    With Application.WorksheetFunction
        func = .Substitute(LCase(func), "x", "xval()")
        func = .Substitute(LCase(func), "exval()", "ex") '(1)
        func = .Substitute(LCase(func), "xval()irr", "xirr")
        func = .Substitute(LCase(func), "xval()npv", "xnpv")
        func = .Substitute(LCase(func), "sumxval()", "sumx") '(2)
        func = .Substitute(LCase(func), "maxval()", "max")  '(3)
        func = .Substitute(LCase(func), "steyxval()", "steyx")
    End With
    Subst = func

Case "Y"
    With Application.WorksheetFunction
        func = .Substitute(LCase(func), "y", "yval()")
        func = .Substitute(LCase(func), "dayval()", "day")
        func = .Substitute(LCase(func), "yval()ield", "yield")
        func = .Substitute(LCase(func), "syval()d", "syd")
        func = .Substitute(LCase(func), "yval()ear", "year")
        func = .Substitute(LCase(func), "hyval()p()", "hyp")
        func = .Substitute(LCase(func), "steyval()x", "steyx")
        func = .Substitute(LCase(func), "besselyval()", "bessely")
        func = .Substitute(LCase(func), "frequencyval()", "frequency")
        func = .Substitute(LCase(func), "sumx2myval()2", "sumx2my2")
        func = .Substitute(LCase(func), "sumx2pyval()2", "sumx2py2")
        func = .Substitute(LCase(func), "sumxmyval()2", "sumxmy2")
    End With
    Subst = func
    
Case "XY"
    With Application.WorksheetFunction
        func = .Substitute(LCase(func), "x", "xval()")
        func = .Substitute(LCase(func), "exval()", "ex") '(1)
        func = .Substitute(LCase(func), "xval()irr", "xirr")
        func = .Substitute(LCase(func), "xval()npv", "xnpv")
        func = .Substitute(LCase(func), "sumxval()", "sumx") '(2)
        func = .Substitute(LCase(func), "maxval()", "max")  '(3)
        func = .Substitute(LCase(func), "steyxval()", "steyx")
    End With

    With Application.WorksheetFunction
        func = .Substitute(LCase(func), "y", "yval()")
        func = .Substitute(LCase(func), "dayval()", "day")
        func = .Substitute(LCase(func), "yval()ield", "yield")
        func = .Substitute(LCase(func), "syval()d", "syd")
        func = .Substitute(LCase(func), "yval()ear", "year")
        func = .Substitute(LCase(func), "hyval()p()", "hyp")
        func = .Substitute(LCase(func), "steyval()x", "steyx")
        func = .Substitute(LCase(func), "besselyval()", "bessely")
        func = .Substitute(LCase(func), "frequencyval()", "frequency")
        func = .Substitute(LCase(func), "sumx2myval()2", "sumx2my2")
        func = .Substitute(LCase(func), "sumx2pyval()2", "sumx2py2")
        func = .Substitute(LCase(func), "sumxmyval()2", "sumxmy2")
    End With
    Subst = func
    
Case Else
    Subst = CVErr(xlErrValue)
End Select

End Function

[ This Message was edited by: Jay Petrulis on 2002-05-12 20:58 ]

[RET79]

Jay,

Again many thanks for your help - I am certainly interested in gathering as much resources as I can about using excel to integrate and solve complicated mathematical equations etc. so if you got some stuff you don't mind sending out then I would be very happy to see it.

I will try and find out tomorrow what exactly the guy was trying to integrate.

Many thanks,

RET79

PS. any websites, books or whatever you can think of out there using excel to do integration, iteration, solving pde's with numerical methods etc. then please let me know.

[Jay Petrulis]

Sample file sent through private e-mail.

Basically, there is not much need for this in Excel, because Mathematica, Maple and many other programs do this so much better. Not sure where there are any Excel related numerical analysis resources, except for the newsgroups. And with that, there is one name that stands out -- Dave Braden. Definitely search for his posts on google.com.

What all this entails is knowing some programming language like C or Fortran and translating the routines in Numerical Recipes to Excel. The complete text of the NR books is available online (don't know the site off hand).

Jay

[Jay Petrulis]

On 2002-04-28 06:25, RET79 wrote:
Essentially, what I am getting at with the above posting is:

When does it become suboptimal to be using worksheet functions and columns to calculate stuff vs. VBA?

Let me give you another example.

We had this formula to calculate something called 'annuity factors'. It relied on 4 different inputs - x1, x2, x3, x4 whose ranges were as follows:

x1 = 16 to 65 (whole numbers)
x2 = 0 to 49 (whole numbers)
x3 = 3,6, or 12 only
x4 = 1,4 or 12 only.

Each combination produced 1 annuity factor value. The inputs were put into a lot of equations based on a lot of huge tables, since it was all column based calculations.

I did a macro to generate all possible combinations i.e. 49*49*3*3 = 21,609 values.

It took 9 minutes, so it was generating roughly 2400 values per minute.

The macro was based on 4 nested loops for each input. The annuity factors were put on another sheet using cell.offset functions.

Now, I feel that 9 minutes is a long time to do such a task. It was 9 minutes after defining all variants, disenable screen updating etc.

I am guessing that doing more calcs via VBA rather than on the worksheet for this task will greatly reduce the time.

Perhaps it would be helpful to know what is the MAX values you can loop through in one minute, so that I have an idea of how fast excel can work.

OK, sorry for going on about this so much, thanks for reading.

RET79

Post your routine that took 9 minutes to calculate. I am positive that the problem wasn't the routine itself, but rather the other formulas on the worksheets that were recalculating.

If it is possible, set the calculation setting to manual at the start of the routine and back to automatic at the end.

The following routine multiplies each element and writes it to column A of the worksheet. It took me less than 2 seconds on each test run.

'---------------
Sub test()
Dim a, b, c, d, e, f, counter, time1, time2

time1 = Timer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

c = Array(3, 6, 12)
d = Array(1, 4, 12)

counter = 1

For a = 16 To 65
For b = 1 To 50
For e = 0 To 2
For f = 0 To 2
Cells(counter, 1) = a * b * c(e) * d(f)
counter = counter + 1
Next f
Next e
Next b
Next a
time2 = Timer
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox time2 - time1

End Sub
'---------------------

BTW, you have 50*50*3*3 = 22500 elements, not 21609, as your ranges are inclusive of the endpoints (unless you are explicit here in your actual file).


Bye,
Jay

EDIT: Faster sample code using an array

Sub test()
Dim a As Integer, b As Integer, c, d
Dim e As Integer, f As Integer, counter As Long, time1, time2
Dim MyArray

time1 = Timer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

c = Array(3, 6, 12)
d = Array(1, 4, 12)
counter = 1
ReDim MyArray(1 To 22500, 1 To 1)
For a = 16 To 65
    For b = 1 To 50
        For e = 0 To 2
            For f = 0 To 2
                MyArray(counter, 1) = a * b * c(e) * d(f)
                counter = counter + 1
            Next f
        Next e
    Next b
Next a

Range("A1").Resize(22500, 1) = MyArray
time2 = Timer
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox time2 - time1

End Sub

[ This Message was edited by: Jay Petrulis on 2002-05-24 19:31 ]

[RET79]

Jay,

Thanks for your comments, I will reply properly tomorrow when I get the file from work, unfortunately I dont have it on my laptop here.

RET79