Macro for averaging
 

Hello All:

Would you please help with macro to average all the data and put in a new
sheet.

1/ "Rawdata" sheet contain all the data
2/ Average 3 rows starting from row 2 to 2000 (let say average row 2 to 4,
and 5-7, so on)
3/ Copy and paste in a new sheet lets called "Summary" at column 3 row2
4/ Delete all the blank rows

Thanks for all your help!

Macro for averaging
 

I don't know what you are refereing to by blank rows.

You can use a formula for getting the averages if you are smart by using the
row number of where the formula is located.

On the summary sheet starting a row 3

the start row on RawData is where X is the Row number

For Row 2
Start Row = (3*(X-2)) + 2
For X:3 = 2 X:4 = 5 X:5 = 8 X:6 = 11

End Row = (3*(X-3)) + 4
For X:3 = 4 X:4 = 7 X:5 = 10 X:6 = 13

So a formula could be for Row 2
=SUM(INDIRECT("RawData!"&((3*(ROW(C2)-2)) + 2)&":"&((3*(ROW(C2)-2)) + 4)))

Row 2


Row 3
This formula will produce =Sum(Indirect("RawData!5:7"))


Row 4
This formula will produce =Sum(Indirect("RawData!8:10"))

Then copy formula dow the column.

"bioyyy" wrote:

Hello All:

Would you please help with macro to average all the data and put in a new
sheet.

1/ "Rawdata" sheet contain all the data
2/ Average 3 rows starting from row 2 to 2000 (let say average row 2 to 4,
and 5-7, so on)
3/ Copy and paste in a new sheet lets called "Summary" at column 3 row2
4/ Delete all the blank rows

Thanks for all your help!

Macro for averaging
 

Joel:

Great formula. However, the value give me so different if I do manuallly
(average(Rawdata!C2:C4)? Any suggestions

Thanks,



"Joel" wrote:

I don't know what you are refereing to by blank rows.

You can use a formula for getting the averages if you are smart by using the
row number of where the formula is located.

On the summary sheet starting a row 3

the start row on RawData is where X is the Row number

For Row 2
Start Row = (3*(X-2)) + 2
For X:3 = 2 X:4 = 5 X:5 = 8 X:6 = 11

End Row = (3*(X-3)) + 4
For X:3 = 4 X:4 = 7 X:5 = 10 X:6 = 13

So a formula could be for Row 2
=SUM(INDIRECT("RawData!"&((3*(ROW(C2)-2)) + 2)&":"&((3*(ROW(C2)-2)) + 4)))

Row 2


Row 3
This formula will produce =Sum(Indirect("RawData!5:7"))


Row 4
This formula will produce =Sum(Indirect("RawData!8:10"))

Then copy formula dow the column.

"bioyyy" wrote:

Hello All:

Would you please help with macro to average all the data and put in a new
sheet.

1/ "Rawdata" sheet contain all the data
2/ Average 3 rows starting from row 2 to 2000 (let say average row 2 to 4,
and 5-7, so on)
3/ Copy and paste in a new sheet lets called "Summary" at column 3 row2
4/ Delete all the blank rows

Thanks for all your help!

Macro for averaging
 

The formula should be

from
average(Rawdata!C2:C4)
to
average(Rawdata!2:4)

You are only averaging 3 cells not the entire row.



I would first try SUM instead of Average to see if sum works. when an
average is performed the number of cells that it divides by may be incorrect.
I'm not sure what criteria excel uses for the division. blank cells may be
making the division incrooect. Also if you have other data in the rows
beside number this may cause problems. Dates will be treated as numbers and
added to the average.

I like using the Evaluate formula tool to help solve problems. Click on cell
with number and then from the menu Tools - formula Auditing - Evaluate
formula..

"bioyyy" wrote:

Joel:

Great formula. However, the value give me so different if I do manuallly
(average(Rawdata!C2:C4)? Any suggestions

Thanks,



"Joel" wrote:

I don't know what you are refereing to by blank rows.

You can use a formula for getting the averages if you are smart by using the
row number of where the formula is located.

On the summary sheet starting a row 3

the start row on RawData is where X is the Row number

For Row 2
Start Row = (3*(X-2)) + 2
For X:3 = 2 X:4 = 5 X:5 = 8 X:6 = 11

End Row = (3*(X-3)) + 4
For X:3 = 4 X:4 = 7 X:5 = 10 X:6 = 13

So a formula could be for Row 2
=SUM(INDIRECT("RawData!"&((3*(ROW(C2)-2)) + 2)&":"&((3*(ROW(C2)-2)) + 4)))

Row 2


Row 3
This formula will produce =Sum(Indirect("RawData!5:7"))


Row 4
This formula will produce =Sum(Indirect("RawData!8:10"))

Then copy formula dow the column.

"bioyyy" wrote:

Hello All:

Would you please help with macro to average all the data and put in a new
sheet.

1/ "Rawdata" sheet contain all the data
2/ Average 3 rows starting from row 2 to 2000 (let say average row 2 to 4,
and 5-7, so on)
3/ Copy and paste in a new sheet lets called "Summary" at column 3 row2
4/ Delete all the blank rows

Thanks for all your help!