Solved

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

Posted on 2006-03-25

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