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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

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.

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.

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

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 trialFor 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

Set st = ThisWorkbook.Sheets("stack

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.Cel

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(mtc

End If

End Function

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.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

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

Is it:

SQRT of SUM (item squared)?

Regards,

Patrick