+ Reply to Thread
Results 1 to 12 of 12

Calculate interval since last positive occurance

  1. 02-13-2017, 04:37 PM #1
    glgold
    glgold is offline
    Registered User
    Join Date
    02-12-2017
    Location
    Florida
    MS-Off Ver
    2013
    Posts
    4

    Question Calculate interval since last positive occurance

    Hello,

    I am trying to calculate the interval between only positive entries on on dynamic table. A sample data set is listed below and attached as a spreadsheet. The interval calculation is in the final column. If the current line has a positive response, the formula will return the number of days since the last positive. If the current entry is negative, a null value is returned. The average interval is calculated elsewhere for planning reasons using: =AVERAGE(C:C)


    Date Ate Interval
    1/5/17 yes 7
    1/12/17 No -
    1/19/17 yes 14
    1/26/17 No -
    2/4/17 yes 16
    2/11/17 no -
    2/17/17 No -
    2/18/17 yes 14

    Average Use Interval: 13
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-13-2017, 05:10 PM #2
    CAntosh
    CAntosh is offline
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Calculate interval since last positive occurance

    Welcome to the board!

    I presume that you'll be entering the first value manually? If that's the case, then try the formula below, entered in D3 and filled down:

    =IF([@[Ate it?]]="No","-",[@Date]-INDEX([Date],MATCH("yes",[Ate it?],0))-(SUM($D$2:$D2)-$D$2))

    See the attachment to view it in practice:
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 02-13-2017, 05:53 PM #3
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: Calculate interval since last positive occurance

    I tried it this way:

    This code goes in the worksheet's VBA

    Please Login or Register  to view this content.
    For the average I set the formula as follows:

    Please Login or Register  to view this content.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you
    Reply With QuoteRegister To Reply
  4. 02-13-2017, 06:24 PM #4
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Calculate interval since last positive occurance

    Another way. Array enter this in C2. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Dave
    Reply With QuoteRegister To Reply
  5. 02-13-2017, 07:02 PM #5
    Amanda Wilso
    Amanda Wilso is offline
    Forum Contributor
    Join Date
    12-18-2016
    Location
    London
    MS-Off Ver
    Microsoft 2013
    Posts
    145

    Re: Calculate interval since last positive occurance

    Since this is a formula problem, might be easier if If you upload your sheet to google sheets and use the add-ons feature and add Formula Helper. I'm using it myself. It looks for complicated formulas and helps you solve it.. Regarding your question I'll be able to get to it in 10 minutes
    Reply With QuoteRegister To Reply
  6. 02-13-2017, 07:22 PM #6
    glgold
    glgold is offline
    Registered User
    Join Date
    02-12-2017
    Location
    Florida
    MS-Off Ver
    2013
    Posts
    4

    Re: Calculate interval since last positive occurance

    @CAntosh
    I am trying to avoid creating another column. My desire is to have the formula calculate the value for column C so that I do not need to manually calculate it. I modified it for column C and it worked for existing rows but does not fill down when additional entries are added.
    Last edited by glgold; 02-13-2017 at 07:43 PM.
    Reply With QuoteRegister To Reply
  7. 02-13-2017, 08:26 PM #7
    glgold
    glgold is offline
    Registered User
    Join Date
    02-12-2017
    Location
    Florida
    MS-Off Ver
    2013
    Posts
    4

    Re: Calculate interval since last positive occurance

    @FlameRetired
    I will have to look up Array Enter for a mac to test your suggestion. Without the array entry it is not calculating based the interval from the most recent yes.
    Reply With QuoteRegister To Reply
  8. 02-13-2017, 08:39 PM #8
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Calculate interval since last positive occurance

    Without the array entry it is not calculating based the interval from the most recent yes.
    Correct. I am not familiar with Mac.

    From reading posts here the method is apparently version specific ... most commonly though ...
    1. from edit mode press and hold the Cmd (daisy graphic?) while hitting Enter.
    2. You will know it is properly committed when you see curly braces ({}) around the formula in the formula bar.
    3. You do not type those in yourself.
    4. If you do you will get an error.
    5. Excel puts them in for you.
    Reply With QuoteRegister To Reply
  9. 02-13-2017, 09:49 PM #9
    glgold
    glgold is offline
    Registered User
    Join Date
    02-12-2017
    Location
    Florida
    MS-Off Ver
    2013
    Posts
    4

    Re: Calculate interval since last positive occurance

    @FlameRetired
    Will the array dynamically enlarge as more rows of data are added?
    Reply With QuoteRegister To Reply
  10. 02-13-2017, 10:17 PM #10
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,452

    Re: Calculate interval since last positive occurance

    Use this in C2 then drag down:

    Please Login or Register  to view this content.
    Quang PT
    Reply With QuoteRegister To Reply
  11. 02-14-2017, 12:55 AM #11
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Calculate interval since last positive occurance

    Quote Originally Posted by glgold View Post
    @FlameRetired
    Will the array dynamically enlarge as more rows of data are added?
    I don't work that much with tables so I can't say with certainty, but since it dynamically filled down (as tables do) and the ranges increased accordingly I believe it will.

    Try it and see.
    Reply With QuoteRegister To Reply
  12. 02-14-2017, 01:13 PM #12
    CAntosh
    CAntosh is offline
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Calculate interval since last positive occurance

    Quote Originally Posted by glgold View Post
    @CAntosh
    I am trying to avoid creating another column. My desire is to have the formula calculate the value for column C so that I do not need to manually calculate it. I modified it for column C and it worked for existing rows but does not fill down when additional entries are added.
    My formula doesn't require another column? You can put it in C2. I used it in D2 in my sample to demonstrate that it returns the expected results. Paste the following in C2:

    =IF([@[Ate it?]]="No","-",[@Date]-INDEX([Date],MATCH("yes",[Ate it?],0))-(SUM($C$2:$C2)-$C$2))
    Attached Files Attached Files
    Last edited by CAntosh; 02-14-2017 at 01:30 PM.
    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. Ajacent cells to calculate next interval by date
    By rcduenes in forum Hello..Introduce yourself
    Replies: 3
    Last Post: 12-08-2015, 10:42 AM
  2. Formula to calculate minutes used in an interval
    By irascari in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-09-2014, 05:14 PM
  3. How to calculate confidence interval??
    By thara1489 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-11-2013, 02:23 AM
  4. Calculate interval and mark it in another column
    By psych216 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 08-20-2011, 03:36 AM
  5. Calculate how many records per 5 minute interval
    By riccaliolio in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2011, 06:58 AM
  6. Calculate month after a varying interval
    By DrWil in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-23-2011, 01:14 PM
  7. [SOLVED] Calculate a number based on an interval
    By Vitalie Ciobanu in forum Excel General
    Replies: 4
    Last Post: 03-19-2006, 04:00 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