Hello,
I have a macro written that collects values into an array, then I do various statistics on them - average, st dev, max, min.
The macro collects data from over 500 workbooks in a folder, taking out specific data, and entering into another workbook database. From there I put the data into an array to do some computations. I don't have anything that filters the data from each workbook to get rid of zeros & errors in the array. I was hoping that there would be some way to ignore them when doing the calculation on the array.
Here is the code for this portion. I tried doing an AverageIf function too filtering for anything ">0", but that did not work.
There are no errors for any undefined objects or anything. Everything works great until I get an array with a div/0 in it because it can't take the average. Ideally I'd like to ignore all div/0 and 0 values.
I have a macro written that collects values into an array, then I do various statistics on them - average, st dev, max, min.
The macro collects data from over 500 workbooks in a folder, taking out specific data, and entering into another workbook database. From there I put the data into an array to do some computations. I don't have anything that filters the data from each workbook to get rid of zeros & errors in the array. I was hoping that there would be some way to ignore them when doing the calculation on the array.
Here is the code for this portion. I tried doing an AverageIf function too filtering for anything ">0", but that did not work.
There are no errors for any undefined objects or anything. Everything works great until I get an array with a div/0 in it because it can't take the average. Ideally I'd like to ignore all div/0 and 0 values.
Code:
wsData.Cells(LastRow, 10).Value = WorksheetFunction.Average(arrCT)
wsData.Cells(LastRow, 11).Value = WorksheetFunction.StDev(arrCT)
wsData.Cells(LastRow, 12).Value = WorksheetFunction.Max(arrCT)
wsData.Cells(LastRow, 13).Value = WorksheetFunction.Min(arrCT)
wsData.Cells(LastRow, 14).Value = wsData.Cells(LastRow, 12).Value - wsData.Cells(LastRow, 13).Value
wsData.Cells(LastRow, 15).Value = WorksheetFunction.Average(arrCOR)
wsData.Cells(LastRow, 16).Value = WorksheetFunction.StDev(arrCOR)
wsData.Cells(LastRow, 17).Value = WorksheetFunction.Max(arrCOR)
wsData.Cells(LastRow, 18).Value = WorksheetFunction.Min(arrCOR)
wsData.Cells(LastRow, 19).Value = wsData.Cells(LastRow, 17).Value - wsData.Cells(LastRow, 18).Value
Last edited:
