## Sorting data and averaging multiple and uneven numbers of rows

523 Views 20 Replies Latest reply: Feb 20, 2013 7:12 AM by diveo
• Level 7 (27,375 points)

The technique I used to remove the duplicates is similar to Badunit's. I'll give you my approach:

Add a column to the left of the date/time column

In row 2 of the new column, write:

=OFFSET(A2, -1,1)=OFFSET(A2, 0,1)

Copy this cell and Paste to the rest of the column.

All the duplicate rows will be identified with TRUE.

Copy the entire aux column and Command-C.

Edit > Paste Values.

Sort the comparison column ascending.

Delete the rows containing TRUE.

Delete the Aux column.

Jerry

• Level 7 (28,805 points)

Hi John,

I took a stab at this before reading the rest of the thread.

Looking at the results of copying your sample data from the thread, then pasting it into a Numbers table, my impression is that the Date and Time values are each in a single cell. Gven that, I'm surprised at the frequency of duplicates identified by Badunit's formula.

An issue with the gaps in the data is that dayswith measurements at one hour intervals will outweigh days with data at two hour intervals if all non-duplicate data is included in a simple average calculation.

My approach was to calculate an average for each date, then use that daily average in the calculation of an annual average. Example below uses on the sample data you provided, but will also work with the full table.

Column A has been added to contain the date of each sample, with the time of day for all samples on that date set to 00:00:00.

On the separate, small table, averages have been calculated for each date using AVERAGEIF. In a Footer row, the 'annual' average has been calculated using the Daily average data.

Formulas:

A2 and filled down (to A25): =DATEVALUE(B2)

A26 and filled down:            =DATEVALUE(LEFT(B26,10))

The two formulas were necessary in my tables as Numbers recognized the first rows as date and time values, but saw the December values as text. This might be an artifact of my custom date format. You may need only the first formula.

Small table:

Column A contains entered dates. these are Date and Time values, with the time set (by Numbers) to 00:00;00, used to match the D&T values in column A of the large table.

B2, filled down to B5: =AVERAGEIF(Table 2 :: \$A,A2,Table 2 :: \$F)

This calculates the average wind speed (in m/s) for each date in  column A, giving a single value for that date.

B6: =AVERAGE(B)

This calculates the annual average, with equal weight given to each date on which measurements were recorded.

Row 6 is a Footer Row.

Regards,

Barry

• Level 7 (27,375 points)

diveo wrote:

Hi Jerry,

Thanks for joining in. The reason for averaging the daily wind data, is to allow a comparison with the average daily electricity consumption from a different data source. I cannot obtain consumption data with any greater resolution than daily, but the wind data (and ultimately the energy that can be derived from the wind via a turbine), has been supplied in hourly format.

For the purposes of comparing supply with demand a daily average will be adequate, as the aim of the work is to consider the surplus / deficit and mitigation measures, i.e. export to grid, storage capacity required or additional electical energy to be purchased. The assignment is to consider the financial viability of a community owned wind turbine.

Hope that is sufficient. I will try to mail the data to you separately, I am not clear on whether I can attach it here directly.

Thanks again...

John,

Once you have a handle on the data, you can do lots of analysis. The daily averages can be calculated using AVERAGEIFS. Here's an example:

The trend line is a 7-day moving average, so following that takes out some of the noise and gives you an idea of the trends by week.

Jerry

• Level 6 (10,515 points)

Diveo,

Sorry I didn't get back to you sooner. I got pulled away and had to attend to other things.  It looks like Jerry is geating to the heart of the matter so I'm going to leave you in his capable hands.

• Level 7 (27,375 points)

John,

You can see that we're starved for good data examples here .

Here's my favorite, and I'll leave you alone after this...

Jerry

Previous Next

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.