Using dates to "count" rows those that coincide?

[domgilberto]

Please see the excel spread-sheet attached.

Each row is a forex trade.

I want to know what the MAX COUNT was for open trades at the same time by looking at the open and close dates column.

Really appreciate any help on this

Thanks!

[domgilberto]

Anything ?

[Sam Capricci]

your spreadsheet isn't very helpful and your question is not clear enough for someone looking at it to understand what you need.
perhaps a sample of what you are looking for on the spreadsheet?

[JeteMc]

I believe the logic would be to count the intersection of all of the transactions, up until that point in time, that close after a the transaction in question has opened AND open before the transaction in question has closed. To that end I sorted the transactions by "OPEN DATE" and applied this formula:

Please Login or Register  to view this content.

to your file updated (attached). I have only manually verified the results through row 34, so you may want to make sure that the formula is acting a expected for the remainder of the transactions.
Copy of test-2.xlsx
I found a lot of dates that were actually formatted as text so they had to be changed to a value before the formula would work. There are places where the trade closes before it opens, notably row 472, which causes the numbers to look anomalous.
Let me know if you have any questions.

[domgilberto]

Sorry, as Sambo Kid has explained, I wasn't clear enough in my first post.

Here is a new spread-sheet.

Hopefully this will explain it better?

Let me know if this is still ambiguous and I will try again?

[domgilberto]

Ive made this short video here to make my request a little bit easier to understand: http://screencast.com/t/dzAvjKZRkR

Let me know your thoughts or whether or not this helped?

[FlameRetired]

I think this formula is valid, but please check rows 74 and 75. I’m not so confident there. If I understand the concept row 75 should be a 3?

Array-enter this formula in F3 and fill down.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

[JeteMc]

I had not realized the the original dates were in dd/mm/yyyy hh:mm format. In the attached file I have changed the dates so that they are in mm/dd/yyyy hh:mm format. I am still utilizing the formula

Please Login or Register  to view this content.

pasted in F1 and copied down, by double clicking. I get the same results as were given in the video for F16 and F17, also have checked a few others. Here is the file:
Copy of test-2.xlsx
Let me know if you have any questions.

[newdoverman]

In column F enter this formula to identify duplicate date/times with Symbol

Please Login or Register  to view this content.

Enter this ARRAY formula where you want the max count for simultaneous trades:

Please Login or Register  to view this content.

..confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. . You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.


The Close dates and times were a mess. I inserted temporarily several columns next to the Close Date/Times and selected the Close Date column and the used Text to Columns and separated the dates from the times in the format M/D/Y. I then added the converted dates to the times to create proper date/time combinations. I didn't bother with column A as the times were not going to be included in the calculations.

[FlameRetired]

domgilberto,

Please look at rows 65, 472, 507, 510, 512 and others. Open dates come after close dates.

[domgilberto]

Hey everyone, thank you all for your response.

Just wanted to pop in and say that when I get a chance to review your messages I will let you know if they've helped (as I have yet to get around to revisiting this problem i have!)

[domgilberto]

Please see attached spreadsheet "test".

I have fixed Closed and Open Date columns.

Using JetMC formula I have one sheet working and then on the other sheet with all the data (inside the attached spreadsheet), it doesn't appear to be working?

I sincerely apologize if I am still being vague for what it is I require. Please feel free to tell me if you want more information on what I need.

I am hoping this attached spreadsheet makes it very clear?

[newdoverman]

What you indicate as incorrect is found on row 2311

	A	B	C	D
2311	04/08/2015 20:54	24/09/2015 13:55	AUDCAD	345

[JeteMc]

The formula looks like it is working correctly to me. The transaction that generates the number in question (345) is open from 8/4/2015 8:54:00 PM until 9/24/2015 1:55:00 PM. If I go to row 1966, which contains the last transaction that would have closed before this one opened, go to the next row (1967) and then highlight all the way down to row 2311, the row containing the transaction in question, the bottom right of the screen confirms a count of 345.
Let me know if you have any questions, and/or if I am misunderstanding what you want the formula to do.

[domgilberto]

Ah I see how you're seeing it now.

You're counting each row (which is not quite right).

This example you have highlighted is just 1 trade. Although it had been opened (chronologically) way "back" on the excel spread-sheet, it's still just 1 "trade". So on the row in question, you'd say: "looking back, were there any trades dates from Open to Close Date, that coincided with this one". If yes, then its trade before + 1. Not count each row.

I'm sorry... i feel like ive done a very crappy job explaining this! Let me know if this helped?

[JeteMc]

See if this formula is accomplishing what you want:

Please Login or Register  to view this content.

Notice in the attached copy of your file that I put this formula in D4 and double clicked it down assuming that D3, the count of the first trade, will have to be 1.
Using dates to count rows test-3.xlsx
Let me know if you have any questions or if I have misunderstood.

[domgilberto]

I think you've nailed it sir.

Thank you kindly for your help and patience. Thank you to everyone else on this thread in helping me out too!