Combining Coinciding Results.

[london735]

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!)

[EducatedFool]

You can use the formulas like the following:

    Cell: H9      Formula  (style A1):   =DATE(A9,B9,C9)-DATE(D8,E8,F8)
                  Formula  (style R1C1): =DATE(RC[-7],RC[-6],RC[-5])-DATE(R[-1]C[-4],R[-1]C[-3],R[-1]C[-2])

    Cell: H10     Formula  (style A1):   =DATE(A10,B10,C10)-DATE(D9,E9,F9)
                  Formula  (style R1C1): =DATE(RC[-7],RC[-6],RC[-5])-DATE(R[-1]C[-4],R[-1]C[-3],R[-1]C[-2])

For multiplying results of the overlapping events use

    Cell: J8      Formula  (style A1):   =PRODUCT(I8:I1901)
                  Formula  (style R1C1): =PRODUCT(RC[-1]:R[1893]C[-1])

[london735]

Hello! Thanks for your help - I guess the formula should subtract the dates in the reverse order. Also as you see there can be MORE THAN ONE event which overlap - do you think that formula that you provided can account for this - or it is better done with the array formulas?

[SAS888]

I can offer other example with user's function.
See attachment. Pay attention to formula in cell "J7".

[london735]

thank you for your help) can you please explain this formula? do you think it is possible to do it in such a way so that the individual combination results are visible? Many thanks as usual)

[SAS888]

This is user function, as I spoke already. Here is code to this function, which is found in software module:

Function Prod(rng As Range) As Double
    Dim i As Long, a()
    a = rng.Value: On Error Resume Next: Prod = a(1, 7)
    For i = 2 To UBound(a, 1)
        If CDate(a(i, 1) & " " & a(i, 2) & " " & a(i, 3)) >= _
        CDate(a(i - 1, 4) & " " & a(i - 1, 5) & " " & a(i - 1, 6)) Then Prod = Prod * a(i, 7)
    Next
End Function

That You not understandable here? The Function Prod(range) must have 7 columns without fall. The first 3 columns - there is begin each period (year, month and day accordingly). The following 3 columns - there is end of each period (in same desksides). The seventh column - there is result. The function places whole specified range in array. Then, is organized cycle, in which we search for the overlapping a periods, and if we find not overlaying periods, that multiplies the accumulating result (Prod).

[london735]

Dear Sas,
thank you for your kind explanation. I have to tell you that I need the individual results of the overlapping events recorded in a separate column so that I can review them when I need this....Can you please help me to do this?)
Thanks!)

[SAS888]

The user formula, which I offer, solves your problem without additional columns and cells. EducatedFool gave the decision by means of formulas for the individual results already. Is it made wrong something?

[london735]

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!)

[motorway]

What events do you mean?