I have a set of data that have several groups. Each group has three columns
and each column has hundreds of rows. For each row in each group, I want to
calculate the average of the three columns and put the result in a new sheet.
So that each group will have only one column (the average). Can someone
help me how to achieve this using VB script? Thank you very much.

I'm not sure why you want to do this in VBA when it is very siimple using
excel functions? It can be done in VBA, but it is harder.

You can do an average by simply using the insert function menu item and then
copying the formula down a column like this formula
=AVERAGE(Sheet1!A2:C2)

"turen" wrote:

I have a set of data that have several groups. Each group has three columns
and each column has hundreds of rows. For each row in each group, I want to
calculate the average of the three columns and put the result in a new sheet.
So that each group will have only one column (the average). Can someone
help me how to achieve this using VB script? Thank you very much.

If I understand you correctly, your data has group 1 in columns A, B and C
rows 1-X, group 2 in columns D, E and F rows 1-Y, group 3 in rows G, H and I
rows 1-Z

You want a new sheet with the avg of group 1 in column A, avg of group 2 in
col B, avg group 3 in col C and so on. Do you need the values to be live so
that changing the source changes the totals or will you rerun the macro?

Assuming you are going to rerun the macro try this:
Sub GenAvg()

Dim source As Worksheet
Dim target As Worksheet
Dim groupNo As Integer
Dim currRow As Long

Set source = ActiveSheet
Worksheets.Add
Set target = ActiveSheet

groupNo = 1

While Not IsEmpty(source.Cells(1, (groupNo - 1) * 3 + 1))
currRow = 1
While Not IsEmpty(source.Cells(currRow, (groupNo - 1) * 3 + 1))
target.Cells(currRow, groupNo) = (source.Cells(currRow, (groupNo - 1)
* 3 + 1) + source.Cells(currRow, (groupNo - 1) * 3 + 2) +
source.Cells(currRow, (groupNo - 1) * 3 + 3)) / 3
currRow = currRow + 1
Wend
groupNo = groupNo + 1
Wend

End Sub



"turen" wrote:

I have a set of data that have several groups. Each group has three columns
and each column has hundreds of rows. For each row in each group, I want to
calculate the average of the three columns and put the result in a new sheet.
So that each group will have only one column (the average). Can someone
help me how to achieve this using VB script? Thank you very much.

I assumed they don't know how many rows there will be in each group and
didn't want extra formulas

"Joel" wrote:

I'm not sure why you want to do this in VBA when it is very siimple using
excel functions? It can be done in VBA, but it is harder.

You can do an average by simply using the insert function menu item and then
copying the formula down a column like this formula
=AVERAGE(Sheet1!A2:C2)

"turen" wrote:

I have a set of data that have several groups. Each group has three columns
and each column has hundreds of rows. For each row in each group, I want to
calculate the average of the three columns and put the result in a new sheet.
So that each group will have only one column (the average). Can someone
help me how to achieve this using VB script? Thank you very much.