Results 1 to 2 of 2

Count the number of times a value is LESS than a threshold for a threshold # days, IF..

Threaded View

  1. #1
    Registered User
    Join Date
    12-02-2015
    Location
    CT
    MS-Off Ver
    2013
    Posts
    1

    Count the number of times a value is LESS than a threshold for a threshold # days, IF..

    edit: sorry, previous title was not correct.

    I am trying to modify this array equation...

    Formula: copy to clipboard
    =SUM(--(FREQUENCY(IF(J5:J12422<$S$4,ROW(J5:J12422)),
    IF(J5:J12422>=$S$4,ROW(J5:J12422)))>=$R$4))


    Where J5:J12422 is a column of values, $S$4 is a threshold value, and $R$4 is a threshold number of days. It counts the number of periods in which the value is under the threshold value for at least the threshold number of days.

    I'd like to find a way to modify the equation so that it ignores days that are OVER the threshold unless there are at least 2 consecutive days that are over the threshold. In other words, if the threshold value is 7 and threshold days is at least 8,

    1
    5
    3
    6
    8
    2
    3
    2
    1

    ...I'd like it to skip the 8 and count the period as entirely under the threshold. Even though there are nine data points, it would return a "1"...and if

    1
    5
    3
    6
    8
    8
    3
    2
    3

    ...it would count the two 8's, because there are two consecutive values above the threshold value.
    Last edited by tibug; 11-30-2016 at 12:44 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 12-17-2014, 11:28 PM
  2. [SOLVED] Count number of days a threshold is exceeded
    By Richiemouse in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-20-2014, 02:23 PM
  3. Replies: 2
    Last Post: 05-27-2014, 02:53 PM
  4. Counting periods where threshold is exceeded.
    By aartapple in forum Excel General
    Replies: 3
    Last Post: 03-26-2014, 03:06 AM
  5. Counting number of times a maximum exceeds a threshold on a certain frequency
    By varsakov in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-04-2013, 02:21 PM
  6. Help on Sum of Working Hours + Alerts if exceeded threshold
    By rodcarv in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-15-2013, 06:24 PM
  7. Count periods where threshold is exceeded
    By dieter99 in forum Excel General
    Replies: 7
    Last Post: 04-01-2009, 08:01 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1