Help Filtering with Median Calculation

[ttomred17]

Hi Guys,

First time user, any help would be very much appreciated! Basically I have a years worth of measured Load data for a Electricity substation in 15 minute intervals. This means I have 35,040 points of data, there is some outliers in there and I also need to shrink this down to hourly measurements, so too 8760 points of data. How I want to do this is to run a median filter over every 4 points of data, which will smooth out any outliers I have and also change it from 15 minute to hourly data.

If anyone can help me that would be great!

Thanks

 

[Mackers]

Hi ttomred17

What I would do in this situation would be to first add a column with the median for each hour every time the hour changes. The construction of this formula assumes your data is continuous for the entire year, with your date/time in A2:A35041 and your reading in B2:B35041. It must be executed using Control+Shift+Enter:

=IF(HOUR(A3)<>HOUR(A2), MEDIAN(IF(HOUR(A$2:A$35041)=HOUR(A2), IF(TRUNC(A$2:A$35041)=TRUNC(A2), B$2:B$35041))))

What this does is check if the hour is about to change, then take the median of all values with the same hour on the same day.

Your results in column C will then be either blank or the median for this period, so you should be able to perform some analysis on this.

Hope that helps

Mackers

Edit: for clarity you may wish to add a column that is just the day and hour so you can use pivot tables and suchlike, using a formula like this:

=IF(ISNUMBER(C3), TRUNC(A3)+TIME(HOUR(A3),0,0), "")