Flagging concurrent data

[Hélio]

Hi there!! I´m back with an old situation but with a new aspect. This is regarding Concurrent events. I have a list of events and i know what is the most concurrent event within a period, but is it possible to somehow mark the most concurrent list items? i mean in a list you have i item set as most concurrent, but how to see the concurrent items for that one.

Please see attached to better understand.

 

[Hui]

Helio

Can you explain why Test 3 or 448 is the most "concurrent" event for Day 1
Why isn't I5 or I6 with 556 more concurrent ?

 

[Hélio]

Hello Hui, tks for reply.. Sorry for not have been very clear o the topic.. I will rephrase it.. You are right in your observation regarding the bandwidth usage.. At this moment my point is not necessarily select the most used bandwidth but to know somehow all concurrent events.. In this example day one has most concurrent events in this case those 3 in blue, but if it was 10 or 20 concurrent events, how could i see them?... I´m asking that because in my original table there is one group of events being set as most concurrent with 29 simultaneous occurrence but it`s impossible because i have only 25 systems and if all were set at the same time i would have 25 events maximum.. So this is my necessity to see all concurrent events to understand were the formula is falling.. I´m attaching my original table and highlighted the questionable point, pls see from line 73..

 

[Hui]

I am still confused as to exactly what you want or what you mean by concurrent

G2: =SUMPRODUCT(($B$2:$B$12<C2)*($C$2:$C$12>B2)*($A$2:$A$12=A2))
Copy down

I'm not sure if the highest or Lowest value achieves what you want

 

[Hélio]

Hi Hui, I have a list of events with a start and end time, some events are running at the same time so they are concurrent... The used formula can show us how many times one event is concurring with other but can´t show me with witch events. So when i see one event 29 times concurrent i can´t figure out who are the other 28 events.. This is what I´m trying to understand.. That table i sent to you from line 73 you can see one event set as 29.. it means that in a certain moment it was running at the same time of other 28 events... But its not possible because i have only 25 systems and could have only 25 simultaneously events... So i would like to have those 29 events somehow marked to allow me see where is the problem.. The formula is checking and adding something that is not part of that group..

 

[Hélio]

Sorry i think i didn´t sent you the correct table.. Please see attached the correct one now..

 

[Hui]

Column I is text which doesn't help but if you convert it to times
In O2: or a new column T2:
=SUMPRODUCT(($J$2:$J$416<K2)*($K$2:$K$416>J2)*($I$2:$I$416=I2))
Copy/paste down to row 416

The highest number in each date block has the most clashes with other times in it's Date block
It doesn't need the 2 helper columns and also includes the date in the criteria, which i think is important

Row 74 now only has 27 clashes instead of 29
There are 29 days labelled 2015-04-08

I'm working on another method

will post later

 

[NARAYANK991]

Hi ,

The helper columns are already taking the dates and times into account.

One more condition which can be included in the formula in column O is that the row number should be different , as in :

=SUMPRODUCT(($P$2:$P$416<Q2)*($Q$2:$Q$416>P2)*(ROW($P$2:$P$416)<>ROW(P2)))

Going by the dates and times , row 74 has 28 overlaps , which are row 71 , and then rows 75 through 101.

Narayan

 

[Hui]

I have added a chart to the file
Type in a date in S4 or clear it
sometime you may need to select S4 again and simply press enter to update it

 

[Hélio]

Hi ,

The helper columns are already taking the dates and times into account.

One more condition which can be included in the formula in column O is that the row number should be different , as in :

=SUMPRODUCT(($P$2:$P$416<Q2)*($Q$2:$Q$416>P2)*(ROW($P$2:$P$416)<>ROW(P2)))

Going by the dates and times , row 74 has 28 overlaps , which are row 71 , and then rows 75 through 101.

Narayan

Hello Narayan, This formula is really better... But regarding the overlaps.. I think I´m missing something... We are talking about day 8.. If i have only 25 systems I can´t have 28 simultaneously overlaps, if i start and end all them at the same time i can have only 25 overlaps.. Or I´m reading it in a wrong way?

 

[Hélio]

I have added a chart to the file
Type in a date in S4 or clear it
sometime you may need to select S4 again and simply press enter to update it

Hello HUi... You rally knows those facilities... The graph is really amazing I´m still understanding that.. I revert to you soon...

 

[NARAYANK991]

Hello Narayan, This formula is really better... But regarding the overlaps.. I think I´m missing something... We are talking about day 8.. If i have only 25 systems I can´t have 28 simultaneously overlaps, if i start and end all them at the same time i can have only 25 overlaps.. Or I´m reading it in a wrong way?

Hi ,

Since you say Hui's solution is the one for you , I think we can ignore my comment ; I have no idea what you are talking about , when you talk of events , days , dates and times ...

Narayan

 

[Hui]

My approach is graphical in nature and won't give specific numerical answers
I also struggle with the nomenclature being used

 

[Hélio]

Hello team, first of all i need to thank you all for this valuable help.. Hui and Narayan you both are really excel Ninjas and I´m learning a lot with you.
Narayan, I´m using your formula and table since the beginning and this is really good. Hui your contribution adding facilities is really amazing... To have you both thinking with me is really a honor... Now let me try to put the terms in a way that we all can concentrate forces in the same direction...

What i Have : 25 Systems (Column G).. These system can be turned on in a way that one can overlap the other causing bandwidth (column M) concurrence. During this concurrence period if we sum the bandwidth utilization in that moment we have the bandwidth concurrence pick..

What i need: I need to have a way to show me this pick of utilization.

What is happening: The current used formulas are summing the total overlapping and not only the pick... Please see the same table I´m reverting... Please see lines 78 and 79 (red Arrow).. Those occurrences are about the same system, so they can be summed because one occurrence runs after the other and never at the same time.. So the total sum of bandwidth on Column R is not showing the pick of bandwidth utilization.

Resuming: Its necessary to sum the bandwidth of major number of simultaneous occurrence for different system.

Please help me.. if you feel its better i can perform phone calls and explain personality.. That's would be a pleasure to talk to you both..

My best regards,

 

[Hélio]

Sorry text correction ::: What is happening: The current used formulas are summing the total overlapping and not only the pick... Please see the same table I´m reverting... Please see lines 78 and 79 (red Arrow).. Those occurrences are about the same system, so they CAN NOT be summed because one occurrence runs after the other and never at the same time.. So the total sum of bandwidth on Column R is not showing the pick of bandwidth utilization.