Hello!
I wonder if it is possible to do the following with the help of the formulas alone. I have a list of events which START and END at different times. Some of them start when the other events are still in progress (their end date has not been reached yet). I need a way to combine the results of the events whose times overlap. The results of the overlapping events should be multiplied and stored as a single event. Any help will be hugely appreciated!
Thanks a lot!)


Присоединённый файл ( Кол-во скачиваний: 3 )
 events.zip 30,75 Kb

Examle.
Source table:
START                    END            
YEAR    MONTH    DAY     YEAR    MONTH    DAY    RESULT
1 989      11      9    1 989       12     21    1.0041
1 990       2      8    1 990        2     21    0.9900
1 990       3      8    1 990        4      4    1.0000
1 990       3     22    1 990        4      6    1.0000

Result Table:
START            END            
YEAR    MONTH    DAY    YEAR     MONTH    DAY    RESULT
1 989      11      9    1 989       12     21    1.0041
1 990       2      8    1 990        2     21    0.9900
1 990       3      8    1 990        4      6    1.0000 [result of 1.0000 x 1.0000]
Do I understand it correctly?


But in your file almost all events is overlapping...  

thank you for this code - can you tell me if this file actually works on the immediately preceeding events or it can combine two or more events which overlap??

See attach. Investigate formulas in hidden columns H...M.

Присоединённый файл ( Кол-во скачиваний: 6 )
 EVENTS.zip 166,18 Kb

Akina thank you for your help but what if there are more than one immediately preceding coincidences? I mean when more than one event starts before a previous event - can this also be incorporated into the formulas?)
Thanks!

Добавлено через 14 минут и 38 секунд
dear RockClimber, can you please describe in more detail what your macro is doing ok? I tried to run it and it rearranged the order of the events...

My technique presume all data are sorted by start date and there is NO event pairs where one of them starts after and ends before the other one. To remove the last of above limitations You may construct formula for EndDate calculation too.
But... to solve Your problem when no restrictions exist with VBA coding usage is the best practice IMHO. Formula calculations can not remove intervening rows.

sekira, here a picture with explanation.

What my macro must do (I hope, it works correctly   )

Columns 1 - 3 in your file - start of event, 4 - 6 - end of event. Next line - next event. Column 7 - result of event.
My macro takes 1 row and compares end of first event and start of second event. If second event starts after first event ends, macro goes to next row.
If second event starts before first event ends, macro compares end of first event and end of second event. In this case those events have to be one event, so macro choose bigger end date of events. Then macro deletes (with shift to up) cells with start of second event end lesser date of ends of events. Also macro multiplies event results in column 7.

I hope you can understand my english   

Это сообщение отредактировал(а) RockClimber - 26.8.2009, 09:15

Присоединённый файл ( Кол-во скачиваний: 3 )
 111.gif 9,99 Kb

Hello! Many thanks for your help! Can you please help me to calculate the number of CONCURRENT events which correspond to each of the events which happens (I mean the number of events which occur AT THE SAME TIME). I need this number next to each of the events...Do you think it can be done by formulas alone?
Thank you again very much for your help!)

Event1 interfere with event2 and event2 interfere with event3, but event1 do NOT interfere with event3.

Result is to be 2 or 3?

there can be much more concurrent events - 5 or 6 or 10

it is obvious

It is not that obvious as it seems...I have attached the picture which visualisez teh events.....I need to calculate next to each event how man events are STILL open - started and not finished yet...

Присоединённый файл ( Кол-во скачиваний: 3 )
 OUTPUT21.jpg 117,39 Kb