+ Reply to Thread
Results 1 to 7 of 7

How to determine when a value has stabilized in Excel

  1. 01-13-2015, 03:36 PM #1
    The_Los
    The_Los is offline
    Registered User
    Join Date
    01-13-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    5

    How to determine when a value has stabilized in Excel

    I am trying to find a way to determine when a value has stabilized in excel, in this case, temperature.

    I have a set of time-series data, TIMESTAMP and TEMPERATURE.

    At T0, the temperature begins to rise at a rapid rate due to the temperature hole drilling process - the hole has not yet climatized.

    At T15 it reaches it's max temperature, when the temperature hole begins to cool, the temperature begins to decrease at a rapid pace.

    Until finally, the rate of decrease slows and it begines to stabilize around T26, at which point forward a normal temperature incresae is observed.

    How can I have excel figure out when the temperature is "Stable"?

    TIME TEMP
    t0 10:24 AM 16.39
    t1 10:25 AM 16.13
    t2 10:26 AM 15.64
    t3 10:27 AM 15.64
    t4 10:28 AM 15.91
    t5 10:29 AM 16.26
    t6 10:30 AM 18.28
    t7 10:31 AM 17.67
    t8 10:32 AM 17.28
    t9 10:33 AM 17.24
    t10 10:34 AM 17.32
    t11 10:35 AM 17.31
    t12 10:36 AM 17.61
    t13 10:37 AM 17.85
    t14 10:38 AM 18.02
    t15 10:39 AM 19.61
    t16 10:40 AM 15.91
    t17 10:41 AM 15.10
    t18 10:42 AM 14.68
    t19 10:43 AM 14.43
    t20 10:44 AM 14.21
    t21 10:45 AM 14.00
    t22 10:46 AM 13.85
    t23 10:47 AM 13.81
    t24 10:48 AM 13.74
    t25 10:49 AM 13.70
    t26 10:50 AM 13.56
    t27 10:51 AM 13.71
    t28 10:52 AM 13.66
    t29 10:53 AM 13.71
    t30 10:54 AM 13.65
    t31 10:55 AM 13.67
    t32 10:56 AM 13.66
    t33 10:57 AM 13.68
    t34 10:58 AM 13.74
    t35 10:59 AM 13.79
    t36 11:00 AM 13.81
    t37 11:01 AM 13.80
    t38 11:02 AM 13.81
    t39 11:03 AM 13.83
    t40 11:04 AM 13.90
    t41 11:05 AM 13.96
    t42 11:06 AM 14.13
    t43 11:07 AM 14.18
    t44 11:08 AM 14.32
    t45 11:09 AM 14.33
    t46 11:10 AM 14.38
    t47 11:11 AM 14.34
    t48 11:12 AM 14.44
    t49 11:13 AM 14.41
    t50 11:14 AM 14.48
    t51 11:15 AM 14.49
    t52 11:16 AM 14.59
    t53 11:17 AM 14.59
    t54 11:18 AM 14.63
    t55 11:19 AM 14.72
    t56 11:20 AM 14.77
    t57 11:21 AM 14.82
    t58 11:22 AM 14.87
    t59 11:23 AM 14.92
    t60 11:24 AM 14.94
    Reply With QuoteRegister To Reply
  2. 01-13-2015, 03:58 PM #2
    LJMetzger
    LJMetzger is offline
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: How to determine when a value has stabilized in Excel

    I've done similar things previously by taking a standard deviation for a specific number of points before and after the given row. When the standard deviation gets below an arbitrary value set by you, that can be defined as the stabilization point.

    A typical formula for 15 points (7 points on either side of row 44) would be:
    Please Login or Register  to view this content.
    My speculation is that you would probablly want to use 2 or 3 points on either side of the given row.

    Lewis
    Reply With QuoteRegister To Reply
  3. 01-13-2015, 04:23 PM #3
    MrShorty
    MrShorty is offline
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,410

    Re: How to determine when a value has stabilized in Excel

    In or out of Excel, I, too, have used standard deviation as a possible measure of temperature "stability". When temperature is stable, s should be 0 (plus an error term).

    Another measure I sometimes use is the slope/1st derivative of the data (In Excel, this can be done using the SLOPE() or LINEST() functions). When temperature is stable, slope should be 0 (give or take some error value).

    Sometimes, I prefer a "temp at time t+10 (or whatever time frame would be appropriate) is within (error) of temp at time t.

    Any of these have a subjective "how much error/deviation/variation will I tolerate in declaring the temperature "stable". If this were data from some of my lab work, I would often argue that the temperature was never stable (though maybe an additional hour would be enough to let it become stable). In other cases, I could see three possible regions where the temperature was "stable".+
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.
    Reply With QuoteRegister To Reply
  4. 01-13-2015, 04:29 PM #4
    The_Los
    The_Los is offline
    Registered User
    Join Date
    01-13-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    5

    Re: How to determine when a value has stabilized in Excel

    Thank you Lewis, but standard deviation is not exactly what I'm looking for.

    "Stable" = less than +/-0.5° in 5 previous minutes for 5 consecutive observations (5 minutes)

    I want to flag the first value that meets that criteria.
    Reply With QuoteRegister To Reply
  5. 01-13-2015, 05:00 PM #5
    The_Los
    The_Los is offline
    Registered User
    Join Date
    01-13-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    5

    Re: How to determine when a value has stabilized in Excel

    Thank you MrShorty.

    Can you please provide a quick example of your "slope/1st derivative" method?

    Thanks in advance!
    Reply With QuoteRegister To Reply
  6. 01-13-2015, 06:29 PM #6
    The_Los
    The_Los is offline
    Registered User
    Join Date
    01-13-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    5

    Re: How to determine when a value has stabilized in Excel

    This is what I have so far (in Cell D28):

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 01-13-2015, 06:35 PM #7
    MrShorty
    MrShorty is offline
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,410

    Re: How to determine when a value has stabilized in Excel

    I don't know that there is a single method for this. A lot depends on the details of each situation.

    Example 1: Since times are already every minute, the slope at any given point (in degrees per minute), could be (in E1, maybe) =D2-D1 (assuming the temperature data is in column D), then copy down. Column E then contains an estimate of the slope (in degress per minute) at each time. Then, figure out what your "threshold" will be for determining if it is "stable" (0.5 degree in 5 minutes is about 0.1 deg/min, so maybe highlight the values where ABS(E1)<0.1).

    Example 2: Since you want 5 minutes within 5 degrees, you might take the difference at 5 minute intervals. E1 =D5-D1 (copy down), then highlight the values where ABS(E1)<0.5.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to determine if excel is 32 bit or 64 bit
    By skip2mylew in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-06-2014, 05:43 PM
  2. Problem determine last row. Some files are excel 97 some are excel 2007
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2011, 05:15 PM
  3. How to determine following function in Excel?
    By Eric in forum Excel General
    Replies: 5
    Last Post: 03-05-2006, 10:10 PM
  4. [SOLVED] Determine excel language
    By DS NTE in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-27-2005, 02:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1