filter data

[Sharkies]

Hi everybody,

I need to filter my data from a data base and the criteria for the
removal of such data points includes: (1) if the recorded values are
not within the temperature range 5 °C to 35 °C); (2) if consecutive
temperature data points deviate by >2°C.

The data looks like this:


Time Temperature (°C)

3:40:48 PM 25.5
3:40:49 PM 25.7
3:40:50 PM -42.8
3:40:51 PM 25.6
3:40:52 PM 25.7
3:40:54 PM 25.7
3:40:55 PM 30.8
3:40:56 PM 25.7
3:40:57 PM 25.5
3:40:58 PM 25.8
3:41:01 PM 28.5
3:41:02 PM 25.7
3:41:03 PM 25.7
3:42:01 PM 25.7
3:42:03 PM 25.7
3:42:05 PM 25.6
3:43:23 PM 25.7


The time intervals are not constant and every minute has a different
set of seconds (some times the 60 sec and some times just 3 seconds of
that particular minute).

Is there any formula to sort this?

Thank you very much!

 

[Pete_UK]

Put this in C2:

=IF(OR(B2>35,B2<5),"remove","")

and this in C3:

=IF(OR(B3>35,B3<5),"remove",IF(ABS(B3-B2)>2,"remove",""))

Copy the formula in C3 down the column to cover the data you have in
column B.

Apply autofilter to column C, and select "remove" from the pull_down.
Highlight the visible rows and Edit | Delete Row. Then select All from
the filter pull-down and delete column C.

Actually there is an anomaly in your statement of removing consecutive
cells where the temperature differs by more than 2 degrees. Suppose
you had 24.6, 25.0, 13, 24.5, 24.7 in consecutive cells - the third
and 4th value both vary from their predecessor by more than 2 degrees,
but it could also be argued that the 3rd value is spurious, so if that
is removed then the others do not have a difference greater than 2
deg.

Hope this helps.

Pete