Average ifs for filtered data

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 imputed my data into a spreadsheet I got from someone online.

However, as I try to filter data, the 'Avg Win' in cell I6 does not change. The cell always produces 80 even if rows have been filtered out.

Usually, I would use subtotals for filtered data, but it does not have an averageifs option here.

I have looked online at cases where others have had a similar problem to me; however, cannot seem to replicate their formula solutions in the context of my data.

Any help will be appreciated.

I have attached a visual aid and an excel file!


Thanks
Mikes_KM

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

You may like to take a look at the AGGREGATE function: https://support.office.microsoft.com...ain11.chm60533

Here is the code for UDF Average_If_Special

Function Average_If_Special(AvgRng As Range, CriRng As Range, Criteria As String)
Dim cel As Range
Dim X As Long, Count1 As Long
Dim Sum1 As Double
X = 1
For Each cel In CriRng
    If Not cel.EntireRow.Hidden And cel = Criteria Then
    Sum1 = Sum1 + AvgRng.Cells(X, 1)
    Count1 = Count1 + 1
    End If
    
X = X + 1
Next cel

If Count1 > 0 Then
Average_If_Special = Sum1 / Count1
End If

End Function

To paste the code
Developer --> Visual Basic
VB window opens.
Insert --> Module
Paste the code.
Close the window.

UDF is available in function list.

How did you get on with the AGGREGATE function?

Your attachment is saying it's corrupt and will not open.

Although Caracalla's suggestion was good, it does not fit well with my main spreadsheet.
In the main spreadsheet, I do not want to manually click win/loss(p1) filter as these columns will eventually be hidden.
The win/loss(p1) column should ideally remain untouched.

So it goes back to my original problem of finding a formula that is specific for Avg.Win without me having to manually filter the wins/loss(p1) column for a win in the process.

Reposting my question for clarity:


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.

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

I somehow want to get a numerical value for the Avg.Win from the data in Table1[Total PnL] reflecting the corresponding "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.



Thanks
Mikes_KM

Try this. It is working.
I have added a column CD with heading Total PnL & Win and Formula in CD13

=IF([@[Win/Loss (P1)]]="win",[@[Total PnL]],"")

In I6

=SUBTOTAL(101,Table13[Total PnL & Win])

Have you seen my previous post.