+ Reply to Thread
Results 1 to 4 of 4

Add Last 36 row of data with other condition exclude Zero

  1. #1
    Registered User
    Join Date
    05-14-2008
    Location
    Hong Kong
    MS-Off Ver
    2019
    Posts
    52

    Add Last 36 row of data with other condition exclude Zero

    Hi There,

    Need help, I like to find the last 36 score in the spreadsheets but with the following conditions:

    1. Name
    2. Exclude Zero


    Please see the attached example and appreciate your help.

    I did a search and found the index can do it, but can't figure out how to have the condition with the "name" and "exclude zero"

    Thanks a lot
    Raymond
    Attached Files Attached Files
    Last edited by wpm7113; 04-06-2020 at 08:45 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    53,200

    Re: Add Last 36 row of data with other condition exclude Zero

    Please tell us the results you are expecting to see (manually calculated) in the yellow cells.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    05-14-2008
    Location
    Hong Kong
    MS-Off Ver
    2019
    Posts
    52

    Re: Add Last 36 row of data with other condition exclude Zero

    Thanks AliGW and I have updated the file.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,272

    Re: Add Last 36 row of data with other condition exclude Zero

    Please try at

    L16
    =INDEX(Table1[Score],AGGREGATE(14,6,ROW(Table1)/(Table1[Name]=$H$3),$H$4-ROWS(L$16:L16)+1)-ROW(Table1[#Headers]))

    M16
    =INDEX(Table1[Score],AGGREGATE(14,6,ROW(Table1)/(Table1[Name]=$H$3)/(Table1[Score]>0),$H$4-ROWS(L$16:L16)+1)-ROW(Table1[#Headers]))
    Attached Files Attached Files

+ 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. [SOLVED] MIN value exclude zero with condition from different column
    By erinapurnomo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-02-2019, 01:10 PM
  2. Add EXCLUDE Option in table to Exclude from Solver
    By lbofbb in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-17-2015, 09:05 AM
  3. How to exclude duplicate entries with multiple condition
    By mahershams in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-04-2014, 07:17 AM
  4. Exclude items from Data Validation List based on another columns data
    By amartin575 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-19-2013, 11:53 AM
  5. How to find match data and exclude with condition
    By buvan in forum Excel General
    Replies: 2
    Last Post: 11-17-2011, 06:51 AM
  6. exclude a match condition between two data fields
    By rhiekel in forum Excel General
    Replies: 6
    Last Post: 04-22-2011, 10:53 AM
  7. exclude several values with one condition
    By Brigitte_P in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-31-2007, 12:58 PM

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