Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: A quick general question regarding size of s'sheet

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    can i ask, why so much on the speed, does it really matter that much?
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    Code:
    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 ]

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Code:
    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 ]

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •