time overlapping calculation

[awaisjahangir]

I am calculating trades time in seconds.
So i need to know for how many seconds only 1 trade was happening, for how many seconds 2 trades were simultaneously happening and 3 trades were simultaneously happening and so on.

kindly see the attached file for more clear idea, i need formulas in cells J3, J5 and J7.
Also if it requires VBA coding than go head.

I'll be really grateful for your help.

 

[vletm]

awaisjahangir
terms:?
'durations in seconds'; End - Start like from 9AM to 5PM is 8hrs or what?
'simultaneously'; same time, or what?
'and so on'; means that there could be more than three trades, or what?
or how do You would like to think those?

 

[awaisjahangir]

Vletm, thanks for replying sir,
End start could be like in hours or days,, but i want to have them converted in seconds as u said 9AM to 5PM is 8 hours so i wud multiply 8*24*60*60 to get seconds.

Yes simultaneously means being traded at same time.
and also there are many other trades as well.

 

[awaisjahangir]

Kindly ignore word "seconds" in column "K". As those numbers are not seconds, they are number of trades.

 

[bosco_yip]

Maybe,

1] For how many seconds 3 trades were simultaneously happening, J3 formula :

=SUMPRODUCT(0+((B11:K11<>"")+(B12:K12<>"")+(B13:K13<>"")=3))

2] For how many seconds 2 trades were simultaneously happening, J5 formula :

=SUMPRODUCT(0+((B11:K11<>"")+(B12:K12<>"")+(B13:K13<>"")=2))

3] For how many seconds only 1 trade was happening, J7 formula :

=SUMPRODUCT(0+((B11:K11<>"")+(B12:K12<>"")+(B13:K13<>"")=1))

Regards
Bosco

 

[awaisjahangir]

Thanks for your reply brother Bosco.
I won't work because the data on which you applied formula was just for graphical representation of what I need, main data is in cells A1 to C4.

And there could be any time or dates, e.g. if there is only 1 hours difference between start date and end date there are around 3600 seconds so i can't fill date in 3600 cells.

 

[vletm]

awaisjahangir
Okay, 9AM to 5PM is 8hrs ... but Yours was 9hrs!
Test this...
1) You can add as many rows data as need as in this sample
('Duration in seconds' not update as You didn't need those!)
2) press [Do It]-button [F1]-cell
3) verify results in columns G & H

 

[awaisjahangir]

Veltm, thanks for your help brother it worked.
I want to send you a 20$ gift for your help. Can you help me how can i do so ?

 

[vletm]

Thanks, You could try to do it with this:
https://www.paypal.com/cgi-bin/webscr?cmd=_s-xclick&hosted_button_id=WD8CSMNWZG7YE