MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Need help with Macros and VB

Posted by Jeff on November 15, 2001 10:48 AM

I have a problem that shows each of 60 degrees (60 points) has a different number of data points collected
(i.e., 5 degrees may have 7 associated data points, 6 degrees may have 3,...).
I need a program (macro?) that will put all the data points for "6 degrees"
in a cell, average the cell, then move on to "7 degrees" and do the same...…
This could be done manually. However, the typical data
file has about 2000 data points.
This would be easy if Excel would allow me to write a BASIC program.
Maybe this could be done in a Visual Basic module???

Posted by Catherine Munro on November 15, 2001 1:52 PM

Assuming that I'm understanding the problem correctly, then:

Assume that label "5 degrees" is in A1, "6 degrees" in A2, etc., and you want your "average" results in column B, and that you have a varying number of data points in column C and D and off to the right...

then try this in cell B1:

replacing "IV" with the column for the largest possible number of data points. CountA counts only non-blank cells, so your average will be right no matter how many data points you have.