Need a macro
Posted by Paul DuBay on February 04, 2002 2:27 PM
I have a spreadsheet with 30,000+ rows (containing 20+ cols of data each) of data captured real-time (well, every 1 seconds) from solar calibration equipment (research for ASHRAE). Since opening and manipulating an excel file with so much data is cumbersome on most computers (and I need to share this data with many colleagues), I'm wondering if anyone can help me accomplish the following: I would like to average the data in each row, by column, and condense the data by 1/10. For example, take the values in cells A1:A10, average them, and place the result in cell A1 in a new workbook/sheet. Then, average the data in cells A11:A20, and place the result in cell A2 in a new workbook/sheet....and so on and so forth for all data in all rows.
;-) Any ideas? If I knew some perl, or any other programming language, I'd dump the data to a tab-delimited file and manipulate it that way...but alas, I don't.
Any help is truly appreciated.
Posted by Barrie Davidson on February 04, 2002 3:09 PM
Paul, this was an interesting macro to write. Try this (I think it will work for you):
Sub AverageData()
' Macro written by Barrie Davidson
Dim DataSheet As String
Dim CondensedDataSheet As String
Dim RowCount As Long
Dim ColumnCount As Long
Dim counter As Long
DataSheet = ActiveSheet.Name
RowCount = Range("A1").End(xlDown).Row
ColumnCount = Range("A1").End(xlToRight).Column
Sheets.Add
CondensedDataSheet = ActiveSheet.Name
Range(Cells(1, 1), Cells(1, ColumnCount)).FormulaR1C1 = _
"=AVERAGE(Sheet1!RC:R[9]C)"
For counter = 2 To Int(RowCount / 10)
Range(Cells(counter, 1), Cells(counter, ColumnCount)).FormulaR1C1 = _
"=AVERAGE(Sheet1!R[" & (counter * 10 - 9 - counter) & _
"]C:R[" & (counter * 10 - counter) & "]C)"
Next counter
If RowCount / 10 - Int(RowCount / 10) <> 0 Then
Range(Cells(counter, 1), Cells(counter, ColumnCount)).FormulaR1C1 = _
"=AVERAGE(Sheet1!R[" & (counter * 10 - 9 - counter) & _
"]C:R[" & (RowCount - counter) & "]C)"
End If
End Sub
Regards,
BarrieBarrie Davidson
Posted by Guiderius on February 04, 2002 3:18 PM
You don't need a macro for this.
Assuming your data is on a sheet called "Sheet1", put the following in cell A1 on a new sheet :-
=AVERAGE(INDIRECT("Sheet1!A"&(ROW()-1)*10+1&":A"&ROW()*10))
Fill across and down as far as necessary.
Posted by Barrie Davidson on February 04, 2002 3:20 PM
Doh....
Couldn't see the forest for the trees!!
Nice catch Guiderius
Best regards,
BarrieBarrie Davidson
Posted by Paul DuBay on February 04, 2002 3:21 PM
Paul, this was an interesting macro to write. Try this (I think it will work for you): Sub AverageData() ' Macro written by Barrie Davidson Dim DataSheet As String
:::
:::
:::Wow, that was fast. Barrie, you're the man! Big wet kisses for you. j/k.
:::
:::Thanks a ton,
:::
:::Paul.
