+ Reply to Thread
Results 1 to 3 of 3

Combine List of Events and Values at Regular Intervals

  1. #1
    Registered User
    Join Date
    03-18-2021
    Location
    Ames, Iowa, USA
    MS-Off Ver
    2008
    Posts
    2

    Combine List of Events and Values at Regular Intervals

    Hey there Excel friends,

    I love sitting down to work through Excel masterpieces, but this one really has me really stumped! I have a list of data at 15 minute intervals ("Usage"), over 3 years. And I have a separate list of events with timestamps over those 3 years. I need to evaluate how and how much they overlap (overall minutes, max minutes in one event, overall amount of Usage affected by the events, max amount of Usage affected in one event, etc.). And on top of that, each of the events is assigned to an Area. I need to evaluate the overlap of one area at a time. I made a unique list of the areas, a drop-down, and put a condition in the Event column to evaluate only one area. But I can't figure out how to fill in the Events alongside the regular interval data. I attached the workbook.

    Any ideas?

    Thanks!!

    klain

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Combine List of Events and Values at Regular Intervals

    Trying to make sense of what you are doing here. Let's start with Column C.
    You have =IF(AND(AND(A2 > Events!B2, A2 < Events!C2),Events!A2=O2), B2,0)
    which is comparing the value in A2 only to the first entry in the Events tab. Wouldn't you want to compare it to all the data in Events to see if it falls anywhere in any event?
    If so, your formula (assuming that if it does, you want to return "Usage") should be
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I'm not not sure what you want in Column D or how you would want it calculated.
    So if the time in A2, falls within an event, do you then want to subtract this time from the end time of an event (or a max of 15 minutes)
    What if the time in A2 doesn't fall within an event but somewhere in the next 15 minutes does? Do you want that overlap captured?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    03-18-2021
    Location
    Ames, Iowa, USA
    MS-Off Ver
    2008
    Posts
    2

    Re: Combine List of Events and Values at Regular Intervals

    Yes, COUNTIFS is what I was needing there! Thank you! I adjusted it to only check if the event start time is within the interval:
    =IF(COUNTIFS(feeder,$O$2,Events!$B$2:$B$412, ">="&A3, Events!$B$2:$B$412, "<="&A4), B3,0)

    Regarding what the function returns... If the occurrence of the events lined up with the 15 minute intervals, it would be as simple as returning the Usage. But you're right, it doesn't end up being that simple. Is there a way in the COUNTIFS to return the duration from column D of the Events tab?

    If so, I could do something like =IF(C2<15, C2/15*B2, B2) in row D and adjust the formula in row C to check for overflow in cases that the event lasts more than 15 minutes, like this:
    =IF(C2>15, C2-15, IF(COUNTIFS(feeder,$O$2,Events!$B$2:$B$412, ">="&A3, Events!$B$2:$B$412, "<="&A4), B3,0))

    Thanks again!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. autofill column values based on values at regular intervals
    By indeed in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-09-2021, 05:08 PM
  2. Regular deposits at regular weekly intervals
    By KlahrinzC in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-29-2018, 10:32 AM
  3. Calculating quantiies from irregular intervals to regular intervals
    By abhi1421 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-15-2014, 06:34 AM
  4. Finding y values at regular x intervals
    By tomal in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-13-2012, 08:59 PM
  5. Converting Data in Irregular Time Intervals into Regular Time Intervals
    By AlexJT in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-25-2011, 02:42 AM
  6. Need to Transpose Irregular Time Intervals into Regular Time Intervals
    By AlexJT in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2011, 08:30 PM
  7. [SOLVED] How do I combine a list of dates & a value into 1 week intervals
    By Nathan Woodson in forum Excel General
    Replies: 0
    Last Post: 01-19-2006, 09:40 PM

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