Sorting data and averaging multiple and uneven numbers of rows

If you sort by date (column A) first you can use an IF formula in a new column to flag duplicates.

=IF(A2=A1, "Duplicate","").

You can copy/paste that to the entire column. But you have to sort the table by column A first, before creating this column of formulas. After you have the new column completed, you can then sort by it to get all the duplicates together so you can delete them all at once. I would recommend, though, that if your criteria for duplicates is based soley on the date&time (as I did in the formula above), you first look through the table and determine if they are actually duplicates versus two different data points with the same date and time (not sure how that could happen but I'd still recommend you look).

To get an average of an unspecified number of items, use SUMIF/COUNTIF. We can provide more detail if that isn't enough to get you going.

There is another method for flagging duplicates using the COUNTIF function but I suspect it will be very very slow on a table of 11000 entries.

That is strange.

• What is the highlighting on the table in your screenshot?
• Take a look and tell me what the formula is on the row for 01/01/2013 02:00 (3rd row down). What row is that entry in?
• Is ob_time in column A? Is ob_time completely in column A, not column A is the date and column B is the time? I can't tell these things from your screenshots.

John,

I'm curious how you want to average the wind data. Do you want to average the vector wind velocity, or just the scalar values?

If you want to mail me a copy of the data, I'll take a look at why Badunit's duplicate finding expression isn't working.

Jerry

I am mystified. You can email it to me, too. Click on my name to go to my profile to get my email address.

I was suggesting the formula be slightly different. Row 3 would have =IF(A3=A2, "Duplicate","") . This way you can fill/paste the formula down to the bottom row of the table. With your version of it, the last row will be an error because it will be trying to reference a row that does not exist (the non-existent row below the last row). But that's a different problem.

The two values (A3 and A4) are obviously different when you look at them. I'd baffled as to why the formula would not work. How about this as another test using three cells in that new column:

1. In a cell put the formula =A4 . What's the result? Looks exactly like the value in A4?
2. In another cell put the formula =A3 . What's the result? Looks exactly like the value in A3?
3. In a third cell put =A4-A3 . Is the result 0d ?

Regarding the average, if you have regularly spaced data (like hourly), the average is simple to do. If you have missing data or unevenly spaced data, an average for the day might not be completely accurate.

Time Data

1:00 10

2:00 12

3:00 13

4:00 18

• Average of all four = (10+12+13+18)/4 = 13.25
• If missing the 3:00 data, the average = (10 +12+18)/3 = 13.33
• If you try to interpolate to get the 3:00 data point you would get a different "average"
• If you summed windspeed x "hours until next data point taken" & divided by the total number of hours you would get a different "average". With the missing 3:00 data it would be =(10*1 + 12*2 + 18*1)/4 = 13.00

My suggestion is to do a simple average of the data points you have. If data points are missing, no method will be absolutely correct.

John,

The most significant issue with your plan, I think, is the fact that a simple average of wind speed will have little bearing on the power produced over a 24-hour period, especially if the speed deviation is significant compared to the average.

Here's a typical output curve (Power generated vs. Wind Speed)

Notice that at low wind speeds you have a cube law effect, and at higher speeds the output is clipped because of limitations of the generator and pitch controls.

Jerry

John,

You've got double entries from the first of the year to the end of March. After that, there are just a few duplicates.

I'll return your file with the dups removed.

Jerry

This sort of presentation might be helpful in making your argument. This is your data with the duplicates removed.

Jerry

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