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.
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
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
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))
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks