Finding the Average of Unequally distributed sorted data

**unitedtillidie** · 08-01-2016, 04:23 AM

Hi All,

I have some wind speed data obtained from LIDAR which I need to analyse. The LIDAR takes several wind speed data points every minute... it's not fixed but random - for example from 02:00 till 02:01 it can take 4 data points and from 02:01 to 02:02 it can take 3 data points. I would like to sort this data in 10 mins to be able to find the average wind speed values from say 02:00 till 02:09, 02:10 to 02:19, 02:20 to 02:29 and so on.

I have managed to sort the data into 10 minutes values as per attached excel file. But I'm having trouble using the autofill feature in Excel to find the average wind speed values because the data points are not equally distributed and considering that I have 12 months of data it's unattractive to find the average manually.

I have also attached a sample file. Any help would be greatly appreciated.

Thanks in advanced.

**AliGW** · 08-01-2016, 04:28 AM

Try this in D2 copied down:

=IF(B2=B1,"",AVERAGEIF(B:B,B2,C:C))

**unitedtillidie** · 08-01-2016, 04:37 AM

Thank you ever so much for your kind and fast help! It has worked!!

If I understand correctly, you' are asking excel to perform the average only if the filters are equal, right?

Thanks again!

**AliGW** · 08-01-2016, 04:38 AM

Yes. Basically, at each point that the filter increases, it averages all the data with that new filter number. I presume you have a formula to generate the filter number?

AVERAGEIFS looks at a range and checks that it is equal to the lookup cell, then averages every row that matches the lookup cell.

**unitedtillidie** · 08-01-2016, 04:53 AM

Sorry for bothering you again, but I tried doing them manually to verify the answers... the formula you gave me does not match with the average.

Yes, the formula is =FLOOR(MINUTE(A2),10)

**AliGW** · 08-01-2016, 04:57 AM

Originally Posted by unitedtillidie

Sorry for bothering you again, but I tried doing them manually to verify the answers... the formula you gave me does not match with the average.

Yes, so it seems. No idea why! Sorry.

**Feronen** · 08-01-2016, 05:06 AM

I think, that formula of @AliGW makes the average of EVERY filter in database witch contains the same number (ex. 0), not only in the range of next ten seconds (if I see correctly from @unitedtillidie example, you are making average from every 10 sec. not minutes).

**AliGW** · 08-01-2016, 05:11 AM

Originally Posted by Feronen

I think, that formula of AliGW makes the average of EVERY filter in database witch contains the same number (ex. 0), not only in the range of next ten minutes.

If I step through the evaluation of the formula, it works correctly, identifying just 0 as the filter. I think it may be another manifestation of the floating points issue that is sometimes discussed here.

**unitedtillidie** · 08-01-2016, 05:11 AM

Yes, most probably that is what's happening.

Is there another formula that calculates the average in the range of the next ten minutes? Or alternatively, can I filter the data so that it increments?

**AliGW** · 08-01-2016, 05:13 AM

Right - here's what I would do: change the filter column to a letter (e.g. A for 0, B for 10, etc.) and then my formula will work fine.

If you round up, it's perfect:

=ROUNDUP(AVERAGEIF(B:B,B2,C:C),2)

EDIT: I've just realised that you have the filter value repeating further down! You need to have a DIFFERENT filter value for each ten-minute slot. That's the problem! Doh! Why didn't I see that before ...

**unitedtillidie** · 08-01-2016, 05:20 AM

Originally Posted by AliGW

Right - here's what I would do: change the filter column to a letter (e.g. A for 0, B for 10, etc.) and then my formula will work fine.

If you round up, it's perfect:

=ROUNDUP(AVERAGEIF(B:B,B2,C:C),2)

EDIT: I've just realised that you have the filter value repeating further down! You need to have a DIFFERENT filter value for each ten-minute slot. That's the problem! Doh! Why didn't I see that before ...

Thanks a lot AliGW and Feronen.

One small issue... to replace the filters with letters, do I have to replace them manually? Because I can't use the Find/Replace in case there are wind speed values which are 0, 10 etc...

Thanks again guys

**AliGW** · 08-01-2016, 05:21 AM

You can do find and replace just on that column, but it won't help, as I explained in my edit above. The problem is the filter column. I am just working on an automated solution for you - give me a few minutes.

**AliGW** · 08-01-2016, 05:40 AM

OK - here we go!

In B2, type 0.

In B3, type this and copy down to the last cell in the range:

=IF(MOD(MINUTE(A3),10)=MOD(MINUTE(A2),10),B2,IF(MOD(MINUTE(A3),10)=0,B2+1,B2))

This will increment the filter column by 1 every 10 minutes and allow the AVERAGEIF formula to function correctly.

Use this in column D:

=IF(B2=B1,"",AVERAGEIF(B:B,B2,C:C))

**unitedtillidie** · 08-01-2016, 05:49 AM

Originally Posted by AliGW

OK - here we go!

In B2, type 0.

In B3, type this and copy down to the last cell in the range:

=IF(MOD(MINUTE(A3),10)=MOD(MINUTE(A2),10),B2,IF(MOD(MINUTE(A3),10)=0,B2+1,B2))

This will increment the filter column by 1 every 10 minutes and allow the AVERAGEIF formula to function correctly.

Use this in column D:

=IF(B2=B1,"",AVERAGEIF(B:B,B2,C:C))

Thank you so much AliGW! I have tried the formulas on the real data, checked them and they have all worked! You are the KING :D Thanks again!

**AliGW** · 08-01-2016, 05:50 AM

I hope that I am a queen rather than a king, but you are very welcome!

It may be that someone else will work out a more elegant formula that does the same thing, but at least it works.

**AliGW** · 08-01-2016, 05:50 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**Pete_UK** · 08-01-2016, 05:52 AM

Originally Posted by unitedtillidie

... You are the KING ...

Well, QUEEN probably.

I would have thought it would be more useful to you to have a separate table where you list date/times in 10 min increments and then have the averages in an adjacent column, rather than have large blanks in your table.

Pete

**AliGW** · 08-01-2016, 05:54 AM

Originally Posted by Pete_UK

Well, QUEEN probably.

I would have thought it would be more useful to you to have a separate table where you list date/times in 10 min increments and then have the averages in an adjacent column, rather than have large blanks in your table.

Pete

I agree, Pete, but at least the OP has what they asked for.

**unitedtillidie** · 08-01-2016, 05:57 AM

Originally Posted by AliGW

I hope that I am a queen rather than a king, but you are very welcome!

It may be that someone else will work out a more elegant formula that does the same thing, but at least it works.

Haha, I should have paid more attention to your dp! You're the QUEEN AliGW :D Thanks, once again.

**AliGW** · 08-01-2016, 06:05 AM

Originally Posted by unitedtillidie

Haha, I should have paid more attention to your dp! You're the QUEEN AliGW :D Thanks, once again.

Hehehe! You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Finding the Average of Unequally distributed sorted data

LinkBack

Thread Tools

Rate This Thread

Display

Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Re: Finding the Average of Unequally distributed sorted data

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Keeping a running average of sorted data

interpolation unequally spaced data for stocks quotes in excel

Help finding Average in Data Set

Average Distributed

Plotting data as an area chart with unequally spaced x values

Finding the nearest value of a column that is not sorted.

[SOLVED] Distributed retrival:average value

Bookmarks

Bookmarks

Posting Permissions