We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

# function to calculate root sum square of values in an array

on
Medium Priority
7,083 Views
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
Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT
Top Expert 2010

Commented:
Hi ohmetw,

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

Is it:

SQRT of SUM (item squared)?

Regards,

Patrick

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.

CERTIFIED EXPERT
Top Expert 2010
Commented:
ohmetw,

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

Regards,

Patrick

Not the solution you were looking for? Getting a personalized solution is easy.

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.

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

Else:
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
Access more of Experts Exchange with a free account
##### Thanks for using Experts Exchange.

Limited access with a free account allows you to:

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile