# Combine List of Events and Values at Regular Intervals

1. ## 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. ## 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:
`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?

3. ## 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!!

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

#### Posting Permissions

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

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.05936 seconds
• Memory Usage 8,940KB
• Queries Executed 16 (?)
Template Usage (34):
• (1)footer
• (1)forumrules
• (1)gobutton
• (3)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (1)postbit_attachment
• (3)postbit_legacy
• (3)postbit_onlinestatus
• (3)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php

Hooks Called (49):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_attachment
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit_complete