+ Reply to Thread
Results 1 to 17 of 17

Average ifs for filtered data

  1. #1
    Registered User
    Join Date
    06-15-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    25

    Lightbulb 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
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Mikes_KM; 06-16-2019 at 06:24 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Average ifs for filtered data

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Average ifs for filtered data

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

  4. #4
    Registered User
    Join Date
    06-15-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    25

    Re: Average ifs for filtered data

    Thanks AiiGW,

    I have attached a file to make things easier to understand.

    I will look at the aggregate function in the mean time.

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Average ifs for filtered data

    Here is the code for UDF Average_If_Special

    Please Login or Register  to view this content.
    To paste the code
    Developer --> Visual Basic
    VB window opens.
    Insert --> Module
    Paste the code.
    Close the window.

    UDF is available in function list.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  6. #6
    Registered User
    Join Date
    06-15-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    25

    Re: Average ifs for filtered data

    Thank you for your response.

    I am not well versed on Macros/VBA so will struggle to implement this.

    I am better with formulas.

    However, I do appreciate your help.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Average ifs for filtered data

    How did you get on with the AGGREGATE function?

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

  8. #8
    Registered User
    Join Date
    06-15-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    25

    Talking Re: Average ifs for filtered data

    The aggregate function seems to be similar to the subtotal function. There is no AverageIFS option.
    The file seems to work on my side (Mac).

    I have made a new smaller example version of my excel sheet just so you can see what I am trying to do.

    Please find it attached.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Average ifs for filtered data

    Why not ?

    B2=SUBTOTAL(101,Table1[Total PnL])

  10. #10
    Registered User
    Join Date
    06-15-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    25

    Re: Average ifs for filtered data

    Hi Carracalla,

    Because I am trying to find the average only if is a win.
    B2=SUBTOTAL(101,Table1[Total PnL]) shows the average for both win and loss.

    Thanks Though

  11. #11
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Average ifs for filtered data

    Filter table for win

  12. #12
    Registered User
    Join Date
    06-15-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    25

    Re: Average ifs for filtered data

    Ohh nice, this is an alternative way. Thanks
    I will play about with my original spreadsheet to get it to fit well with my data.

  13. #13
    Registered User
    Join Date
    06-15-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    25

    Re: Average ifs for filtered data

    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.
    Last edited by Mikes_KM; 06-16-2019 at 06:02 PM.

  14. #14
    Registered User
    Join Date
    06-15-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    25

    Smile Re: Average ifs for filtered data

    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
    Attached Files Attached Files
    Last edited by Mikes_KM; 06-16-2019 at 06:49 PM.

  15. #15
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Average ifs for filtered data

    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])

  16. #16
    Registered User
    Join Date
    06-15-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    25

    Re: Average ifs for filtered data

    Hi everyone, I am still struggling with this. I would love to avoid macros please.

    Essentially, I need a formula that writes an Averageif that adjusts for filtered data.

  17. #17
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Average ifs for filtered data

    Have you seen my previous post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. trying to get an average of filtered data
    By greggatz in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-07-2014, 07:20 PM
  2. Average of filtered data
    By Benjamin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 03:05 AM
  3. [SOLVED] Average of filtered data
    By Benjamin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 02:05 AM
  4. Average of filtered data
    By Benjamin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 01:05 AM
  5. Average of filtered data
    By Benjamin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 12:05 AM
  6. Average of filtered data
    By Benjamin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-05-2005, 10:05 PM
  7. [SOLVED] Average of filtered data
    By Benjamin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-03-2005, 10:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1