My first suggestion is to convert the data range into an Excel table and add a column to identify the first occurrence of a trade.
1. Added column is populated using: =COUNTIFS(A$2:A2,A2)=1
2. For Average hold time for long trades:
Formula:
=SUMPRODUCT((tblTrades[Exit Time (CT)]-tblTrades[Entry Time (CT)])*(tblTrades[First Occurrence])*(tblTrades[Long/ Short]="Long"))/COUNTIFS(tblTrades[First Occurrence],TRUE,tblTrades[Long/ Short],"Long")
3. For Average hold time for specific instrument:
Formula:
=SUMPRODUCT((tblTrades[Exit Time (CT)]-tblTrades[Entry Time (CT)])*(tblTrades[First Occurrence])*(tblTrades[Instrument/Symbol]="nq"))/COUNTIFS(tblTrades[First Occurrence],TRUE,tblTrades[Instrument/Symbol],"nq")
4. For Avereage hold time in month of May:
Formula:
=SUMPRODUCT((tblTrades[Exit Time (CT)]-tblTrades[Entry Time (CT)])*(tblTrades[First Occurrence])*(MONTH(tblTrades[Date])=5))/COUNTIFS(tblTrades[First Occurrence],TRUE,tblTrades[Date],">="&N1,tblTrades[Date],"<="&EOMONTH(N1,0))
Note that N1 contains the date: 5/1/2022
5. For Total # of trades in May: =COUNTIFS(tblTrades[First Occurrence],TRUE,tblTrades[Date],">="&N$1,tblTrades[Date],"<="&EOMONTH(N$1,0))
6. For Count all the losing trades in May: =COUNTIFS(tblTrades[First Occurrence],TRUE,tblTrades[Date],">="&N$1,tblTrades[Date],"<="&EOMONTH(N$1,0),tblTrades[Win/ Loss],"Loss")
Let us know if you have any questions.
Bookmarks