PDA

View Full Version : Solved: Average Cells Based on Numbers in Array



chocho
05-29-2013, 07:19 AM
Hi,

What I am doing is generating two random numbers "goodsize" and "badsize" and creating two arrays of the length "goodsize" and "badsize". These arrays are then filled with random numbers. What I now want to do is find the cells in column 1 with the row numbers set as the numbers in the first array, average all of those cells together. Find the cells in column 2 using the numbers in the second array as row numbers and average those cells. Then subtract one from the other. For example, if goodsize=2 and badsize=2 and good(1)=1 and good(2)=3, average cells(1,1) and cells(3,1). If bad(1)=5 and bad(2)=6, average cells(5,2) and cells(6,2). Then subtract these averages.

Here is what I have got so far to generate the arrays:


size = Int((100 - 1 + 1) * Rnd + 1)
badsize = Int((99 - 1 + 1) * Rnd + 1)
goodsize = size - badsize

ReDim good(1 To goodsize) As Long
ReDim bad(1 To badsize) As Long

For l = 1 To UBound(good)
good(l) = Int((20000 - 1 + 1) * Rnd + 1)
Next l

For l = 1 To UBound(bad)
bad(l) = Int((20000 - 1 + 1) * Rnd + 1)
Next l

Any help would be appreciated!

mikerickson
05-29-2013, 07:56 AM
Dim goodSum as Double, goodAvg as Double

'...
For I = 1 to goodSize
goodSum = goodSum + Val(CStr(ActiveSheet.Cells(good(I), 1).Value))
Next I

goodAvg = goodSum / goodSize

chocho
05-29-2013, 08:35 AM
Thanks! Exactly what I was looking for :)