function to calculate root sum square of values in an array

Posted on 2006-03-25
Last Modified: 2012-08-13
I have a MAJOR problem here with calculation time in an excel sheet.  I have a sheet that acts as a table.  In sheet 1, there are unique ids in each row (row A)  and there are about 500 rows of data.   In sheet 2, there are about 4000 rows of data.  For each id in sheet 1, I need to locate each occurence of that id in the table in sheet 2, and root-sum-square all of those values.   Currently, I put an array equation in each cell of column B in sheet 1, the array looks thru the entire range of cells in column A in sheet 2, and root-sum-squares all matching data in column B of sheet 2, and place that result in column B on sheet 1.  I end up having to put calculations to Application.xlManual to keep things moving while procedures run, then turning the calcs back on at the end.   Would it be faster to perform this action in a function?...if so, what would be a good code routine?  ...or is there a better way to increase the performance of this.  THese sigma calculations take 6 seconds to recalculate each time calculations are returned to "automatic"  Please offer direction and code to maximize performance.  thank you
Question by:ohmetw
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Hi ohmetw,

    What is the arithmetic for what you are trying to do?

    Is it:

    SQRT of SUM (item squared)?



    Author Comment

    let's say it is determined that data matching the id is in rows, 17, 22, 290, 424, 755.   the data value pertaining to each row is 4, 5, 2, 1, and 6.

    Here's what I need to calculate    (4^2 + 5^2 + 2^2 + 1^2 + 6^2)^.5       = SQRT(SUMSQ(range))   so this is the idea....problem for me is determining this "range"

    just to clarify further,  each row in sheet 1 has an id value associated with it (row A).   For each row in the sheet2 that contains that value in row 1, I need to root sum square the values in those rows, located in column B.     I have a number of rows.  I have been told that array equations written directly into cells do not calculate as afast as if the operation is done in a function.  so I need some guidance and code ideas for this.    If I were to write it to a function, I would do something like this

    function calculate_sigma
    'Use find function to build an array of "sheet2 column values" that match the "sheet1 col A" id values.

    'then use the VBA equivalent of the =SQRT(SUMSQ(range))  to get the sigma value and write it back to the cell

    End Function

    is this the FASTEST calculating method...if not what is.  calculations are taking far too long with my current method....where I am calculation 600 to 800 array equations in excel cells.

    LVL 92

    Accepted Solution


    I am afraid the array formulas are probably going to be the fastest.  Whoever told you VBA functions would be faster is
    wrong--pretty much always, if there is a way to do a calculation using only native functionality, it will be faster than
    by using VBA.

    A possible exception would be if you could develop something in C++, and then compile it as an XLL (basically a DLL,
    but optimized for Excel).  Those can be incredibly fast.  (In fact, the functions that are enabled by the Analysis ToolPak
    are in reality XLL functions, and there is no discernible speed difference between them and "native" Excel functions.)

    If you really need the speed and do not mind spending some money, take a look at TurboExcel.  It allows you to use
    Excel as a development environment, but then creates C++ executables to actually do the work.  Speed is
    phenomenal, and, because they are compiled executables, they are secure against pesky users trying to change
    things :)



    Author Comment

    well, then I'll try something in a function and minimize the number of rows I am considering.  One problem I am having here is that I specify a large generic range in my cell calculations, where in a function, I could define it easier.....make it only as large as necessary and see increased performance that way.  Could you help me to write the following code properly.  I'm trying to find matches in the table in sheet2  (for each id in sheet1)  so these cells are saved to a range...then I want to get the value in column 7 for each value in the range.....could I just do a
    For Each mtch in MyRange
         MyRange = Offset(MyRange,9).value
    Next mtch

    or something like that?  I am trying to get the appropriate numbers in a range....then root sum square that range.

    please help if you can...thanks

    Function calc_sigma() '(row As Integer)
    Dim row
    row = 4
        Dim rt As Worksheet, st As Worksheet
        Set rt = ThisWorkbook.Sheets("relat table")
        Set st = ThisWorkbook.Sheets("stack table")
        Dim MyRange As Range, mtch As Range, lrow!, mtchvalues
        lrow = rt.Cells(65536, 1).End(xlUp).row  '""
        Set MyRange = Range(rt.Cells(2, 3), rt.Cells(lrow, 3)).Find(What:=CStr(st.Cells(row, 1)), LookIn:=xlValues)
        If MyRange Is Nothing Then
            calc_sigma = 0

             For Each mtch In MyRange
                  'Create array of values
                  mtchvalues = rt.Cells(mtch.row, 10)
             Next mtch
                 'root sum square COLUMN J values for each id in my range
                 'root sum square array
                 calc_sigma = Application.SQRT(SumSq(mtchvalues))
        End If
    End Function

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Suggested Solutions

    Title # Comments Views Activity
    VBA Code Error 3 54
    Vb6 rich textbox tab 10 48
    VBA Modification 5 35
    VBA/SQL - Connect to SQL server and pull data 4 36
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now