Consolidating Data in Microsoft Excel

• Question

• I am trying to consolidate some data using Microsoft excel.  The data consists of a time scale and some data from about 9 different thermal couples.  The DAQ being used collects data every second and collects that data for about 3 hours.  What I want to do is to take a data point every minute but do not want to go through the entire data to collect those points.  Is there a way in Excel to consolidate the date so it will find points every minute and display in a new worksheet?  any help would be greatly appreciated.
Tuesday, June 28, 2011 3:17 PM

• If your data is on sheet 1, with data actually starting on A2 with labels in row 1, then use this formula on another sheet, and you want averages:

=AVERAGE(OFFSET(Sheet1!\$A\$1,(ROWS(\$A\$1:A1)-1)*60+1,COLUMNS(\$A\$1:A1)-1,60,1))

and copy down for 180 rows (3 hours in minutes) and across for 10 columns (1 time and 9 TC values).

If you just want individual data points, starting at row 61 and every 60th row thereafter:

=OFFSET(Sheet1!\$A\$1,ROWS(\$A\$1:A1)*60,COLUMNS(\$A\$1:A1)-1)

and copy down and across in the same manner.

HTH, Bernie
• Marked as answer by Tuesday, July 5, 2011 3:56 PM
Tuesday, June 28, 2011 6:33 PM

All replies

• Lots and lots of options. The easiest? It depends on how your data is written.

Is it a crosstab table

Time   TC1   TC2    TC3

or a database?

Time    TC    Value

For either, you could use a pivot table, with the time stamp used as the row source, the TCs as the data, set to average, and then grouping the data by the minute.'

You could extract one value out of every sixty using formulas.

You could use subtotals set to average, based on changes in the minutes (you might need a helper column, depending on how the time stamps are written).

You could use a macro.

You could simply use all the data - that is only 10,800 data points.

For any of these, it is important to know if you want the 60 data points averaged, or simply sampled by taking one out of the sixty.

HTH, Bernie
Tuesday, June 28, 2011 3:55 PM
• I was trying to figure our to extract the data using formulas but I didn't know how to go about writing the formula whether it be a If then or some other formula.
Tuesday, June 28, 2011 4:27 PM
• If your data is on sheet 1, with data actually starting on A2 with labels in row 1, then use this formula on another sheet, and you want averages:

=AVERAGE(OFFSET(Sheet1!\$A\$1,(ROWS(\$A\$1:A1)-1)*60+1,COLUMNS(\$A\$1:A1)-1,60,1))

and copy down for 180 rows (3 hours in minutes) and across for 10 columns (1 time and 9 TC values).

If you just want individual data points, starting at row 61 and every 60th row thereafter:

=OFFSET(Sheet1!\$A\$1,ROWS(\$A\$1:A1)*60,COLUMNS(\$A\$1:A1)-1)

and copy down and across in the same manner.

HTH, Bernie
• Marked as answer by Tuesday, July 5, 2011 3:56 PM
Tuesday, June 28, 2011 6:33 PM
• You are amazing. Thanks so much for your help!

Friday, August 1, 2014 9:20 PM