How to smartly simplify a spreadsheet with many rows

[MartinLe]

I have the following situation:

I have spreadsheets with measured values for a year, at 5s intervalls (actually I had sveral csv files that I cut into months to reach smaller filesizes). I'D like to reduce the file sizes and further. Ideally I would turn this ...
[tt]
00:00:04 value
00:00:09 value
...
[/tt]

into this:
[tt]
00:00:04 average of previous 5 min
00:05:04 average of previous 5 min
[/tt]

Now I could ad a column that averages the previous 60 lines, but then I'd have to manually remove 59 lines, leave one, etc about 2000 times to edit one week worth of data. Is there a way to do this smarter (not by hand)?

 

[MintJulep]

Turn on the macro recorder.
Do it once.
Modify the recorded macro.
Go.

 

[GregLocock]

Another possibility is to use indirect addressing. This has the advantage that no macros are required, which is not much of an advantage.

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[Denial]

And yet another possibility is to use the OFFSET() function in conjunction with the ROW() and COLUMN() functions.[ ] See attached example (where to keep things small I have averaged in groups of 12 rather than 60, but have left this "group size" as a user-settable parameter).

 

[SkipVought]

You give scant information regarding the structure of your table and I'm assuming that you have a proper table.

My approach, on a new sheet, use MS Query to query your table and summarize. However, since you have told us nothing of 1) the specific table structure or 2) the specific summary logic, I can venture no specific solution. MS Query is an oft used solution in my 25 odd years using Excel.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[IDS]

To keep things simple I prefer to add a helper column to define the row number starting each block of data. The formula for the group average then becomes:

=AVERAGE(OFFSET($F$6,L7,1,$D$3))

where:
F6 is the header cell of the first column of the data
L7 is the first cell of the "helper column"
D3 is the cell with the number of rows for each group
L8 contains the formula: +L7 + $D$3

All as in Denial's spreadsheet.

I have attached a copy of this method to Denial's method (attached).



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Denial]

IDS's version is certainly a big improvement.[ ] Using INDEX() rather than OFFSET() is simpler, more elegant and more understandable.[ ] It also gets rid of those ROW() and COLUMN() functions.

"I dips me lid."

Two trivial comments:
» The results of the formula in column M are not consistent with those in my column I.[ ] To be consistent the "-1" should be removed from the second argument to the INDEX function.
» The formula in column M does not need to be entered as an array formula (at least, not in my Excel 2010).[ ] This change might speed things up very slightly.

 

[IFRs]

Would a pivot table do your job?

 

[IDS]

The formula in column M does not need to be entered as an array formula (at least, not in my Excel 2010). This change might speed things up very slightly.

Yes, agreed. I use array formulas so often, sometimes I use Ctrl-shift-enter just out of habit.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[MartinLe]

I havent had time yet to look at your solutions, thanks all!

 

[bacon4life]

Instead of just 1048576 rows, Excel can handle millions of rows of data when the data is from an external source. You can then summarize the data in a pivot table, as long as you make sure the pivot table never exceeds the normal row limit. Excel should easily be able to do this for CSV file containing a whole year of 5 second data.