Find Average Difference of 2 Columns Based on Criteria from 2 Other Columns

**davidcato** · 08-12-2022, 12:27 AM

Hello,

Can someone please help me derive a formula to take the difference between two columns and find the average, based on two separate column parameters please? I would like the formulas to produce the following results:

1. Average hold time for long trades. Average of difference between column D and C, based only on column A and F. Cannot count trades with same number more than once (column A) and F must be "Long"

2. Average hold time for specific instrument. Average difference between column D and C, based only on column A and G. Cannot count trades with same number more than once (column A) and G must be "NQ" If this formula is the same as #1., please let me know and I will fill in what's needed.

3. Avereage hold time in month of May only. Average of difference between column D and C, based only on column A and B. Cannot count trades with same number more than once (column A) and B is are all those applicable trades in May.

4. Total # of trades in May. Count all trades in May but don't count trades with same number (column A) more than once.

5. Count all the losing trades (column I) in May, but not the trades with the same number more than once (column A)

Please include the entire table including blanks and have formulas ignore blanks. All answers are adjacent highlighted cells where formulas would be entered. Let me know if there's any questions or need for clarification.

Thank you in advance!

**JeteMc** · 08-15-2022, 09:21 AM

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:

Please Login or Register  to view this content.

3. For Average hold time for specific instrument:

Formula:

Please Login or Register  to view this content.

4. For Avereage hold time in month of May:

Formula:

Please Login or Register  to view this content.

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.

**davidcato** · 08-17-2022, 08:47 AM

Thank you JeteMc. Are there formulas that would work without changing it to a data table and adding that new column?

Find Average Difference of 2 Columns Based on Criteria from 2 Other Columns

LinkBack

Thread Tools

Rate This Thread

Display

Find Average Difference of 2 Columns Based on Criteria from 2 Other Columns

Re: Find Average Difference of 2 Columns Based on Criteria from 2 Other Columns

Re: Find Average Difference of 2 Columns Based on Criteria from 2 Other Columns

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Average last n values based on criteria from 2 columns

count cells in 1 column and average based on multiple criteria (different columns)

[SOLVED] Average Based on Multiple Criteria in Same and Different Columns

Getting an average across columns based on criteria.

Average of difference between columns

Calculate average difference between two columns of data [if criteria is met]

[SOLVED] Average Based on Criteria in Multiple Columns

Bookmarks

Bookmarks

Posting Permissions