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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.