excel macro n00b

July 2, 2007 1:27 PM

I need help writing a macro that will divide a 5000 row table into groups of 30 rows, and find the average of certain columns in each group.

I've got a data table with blood pressure readings taken at 1-second intervals. I need help writing a macro that will take the table, divide the data into groups of 30 rows and spit out the average of certain columns in each group (I need one column for systolic, diastolic, MAP, etc., however, the table contains columns of irrelevant data), which will in effect give me averages other 30-second intervals.

To make things more complex, the actual readings start at row 48.

posted by beammeup4 to Computers & Internet (10 answers total)

And your question is....
posted by mrbugsentry at 2:16 PM on July 2, 2007

Response by poster: umm, can someone help me write the macro?
posted by beammeup4 at 2:44 PM on July 2, 2007

You probably want to look at a couple of tutorials for basic excel macros and then just try it.

Assuming you already put some work into this, what specifically is causing the trouble?
posted by mrbugsentry at 2:53 PM on July 2, 2007

Response by poster: i'm just not experienced in programming and don't really have the time to learn everything on my own, thus, I came here to see if any more experienced mefi's would be willing to give me actual help rather than tell me where to go to learn it on my own (which I would most definitely do if I weren't on a deadline)
posted by beammeup4 at 3:39 PM on July 2, 2007

I would highly suggest putting together a sample input and sample output. I'm kind of new to VBScript and enjoy testing myself, but I'd need some details. (Sample rows, example output etc.)
posted by TheDukeofLancaster at 3:58 PM on July 2, 2007

Response by poster: that'll be hard here in mefi, but i'll try to elaborate on what the input is:

this is an excel spreadsheet basically...just raw numbers in a spreadsheet.

to be more specific, there are exactly 5120 rows in the spreadsheet and 26 columns. I need to divide the 5120 rows into groups of 30 rows, and for each group, find the average of columns B,G,H,and I.

So basically, the output will just be another spreadsheet containing 4 columns and ~170-something rows. each row will contain the average over 30 seconds (hence, grouping the original reading into groups of 30).

hope this helps. thanks for any help you're able to offer.
posted by beammeup4 at 4:26 PM on July 2, 2007

Here's a half-baked example of what the code would end up looking like. You need to select all the input data cells before running this macro. It assumes that the output worksheet is named "Output" and the input worksheet is named "Input". I haven't tested it, so I'm not sure if it works.

Public Sub Avg30Row()
  Dim OutputRow As Integer
  OutputRow = 1

  For Row = Selection.Row To ActiveSheet.UsedRange.Rows.Count Step 30
    Worksheets("Output").Cells(OutputRow, 1).Formula = "=average(Input!B" & Row - 30 & ":B" & Row & ")"
    OutputRow = OutputRow + 1
  Next Row
End Sub

posted by zixyer at 5:57 PM on July 2, 2007

For what it's worth, you really don't need a macro for this. You can build up cell references from a string using Excel's Indirect() function. You can use this to computationally generate the ranges. One way to do this would be to have the upper and lower limits of each range in columns A and B, then do =average(indirect("Input!B" & A1 & ":B" & B1)) in column C.
posted by zixyer at 6:07 PM on July 2, 2007

Response by poster: i'll try your second suggestion, zixyer. sort've defeats the purpose of writing a macro if you have to select groups of 30 rows for over 5000 rows x__X
posted by beammeup4 at 6:13 PM on July 2, 2007

Well, to use the macro you would select all the data and run the macro once, not each 30 row partition. But it's always better not to use macros if you don't have to.
posted by zixyer at 6:16 PM on July 2, 2007