Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4537
  • Last Modified:

function to calculate root sum square of values in an array

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
  • 2
  • 2
1 Solution
Patrick MatthewsCommented:
Hi ohmetw,

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

Is it:

SQRT of SUM (item squared)?


ohmetwAuthor Commented:
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.

Patrick MatthewsCommented:

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 :)


ohmetwAuthor Commented:
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

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now