Feb 20, 2008

Situation: I need to calculate a lot of averages of non-connected values. That is, they are separated by several rows (which also contain values). So far, I use the code-sample below (just to illustrate), which is not really elegant. Unfortunately, it seems that this technique comes to its limits when a high number of different non-connected values are combined into one string and get assigned to a formula- range (last line of code), which is where the program stops.

My question is: Is there a nice way to solve this? A way, which is not limited by the amount of data processed? The only way I could think of, would include a UDF, where a sum, sums all values up and then devides by the number of added sums ... but this would be relatively slow, and I have an awful lot of values to be averaged (100 sets * 6 rows * 200 values = 120.000 calculation- steps), as well as STDEV and a Standard Error of Mean (SEM) to calculate. Is there a better alternative?

Const intCells=9 'actually, this is no constant but a variable

Const i=4 'actually, this is no constant, but a nested for- loop-value

Const intLinesInBetween = 12 ' amount of lines, which separate my values

Dim j As Integer 'simple counter, runs through all cells

Dim rngUpperRow As Range 'Range of the upper row = normalized time vector

Dim rngCurrentRowAvg As Range 'current row into which avg formulas are inserted

Dim intCells As Integer ' amount of experiments in range............

View 4 Replies
View Related