Average ifs for filtered data (visible data)

[Mikes_KM]

Hi Everyone,

This is my first post here!

I am trying to play around with data on a trading strategy I have been working on.

I have attached my problem into a new spreadsheet. The real original spreadsheet will be too large to attach here.

What I need:

I want to be able to filter data e.g. the currency pair or data column so that the Avg.Win Cell reflects the change.

I am currently using an average ifs formula, but this does not change the Avg.Win cell when I filter data (it remains at 39.5)
Note: Ideally, I do not want to manually filter the win/loss(p1) column as part of my solution. In the original spreadsheet, this column is hidden.



Thanks
Mikes_KM

 

[Mikes_KM]

Sorry I have realised that I cant add an attachment

However, so far the formulas are as follows:

Avg.Win - =IF(D4="","",AVERAGEIFS(Table1[Total PnL],Table1[Win/Loss (P1)],"win"))

I somehow want to get the average win from Table1[Total PnL] but only if it is a "win" as shown in Table1[Win/Loss (P1)]

I hope this makes sense. The types of things I will be filtering include 'Date' and 'currency pair'. I do not want to manually filter the Table1[Win/Loss (P1)] column as part of this solution.

 

[sandy666]

Sorry I have realised that I cant add an attachment

you can post a link to shared excel file with source data and expected result. use OneDrive, GoogleDrive or any similar.

 

[Mikes_KM]

I have included a shareable link. I have set the restrictions to 'can comment' but not edit, to avoid multiple people changing it.
https://drive.google.com/file/d/1meBsizN1gZh7FNH0u6r6qF3oFUqMKEn6/view?usp=sharing

thanks

 

[Mikes_KM]

you can post a link to shared excel file with source data and expected result. use OneDrive, GoogleDrive or any similar.

Please see https://drive.google.com/file/d/1meBsizN1gZh7FNH0u6r6qF3oFUqMKEn6/view?usp=sharing