+ Reply to Thread
Results 1 to 5 of 5

Exclude zero records in results

  1. #1
    Registered User
    Join Date
    04-27-2019
    Location
    Northern Ireland
    MS-Off Ver
    2019
    Posts
    62

    Exclude zero records in results

    Hi,


    Just need to exclude any cell that are '0' (zero) in the result. I did try to modify but without much success.

    MIN(5,IFERROR((MATCH(2,1/($M3:$AA3<0))-MATCH(2,1/($M3:$AA3>0)))*(INDEX($M3:$AA3,MATCH(25^25,$M3:$AA3))<0),COUNTIF($M3:$AA3,"<0")))

    I haven't included a sample sheet as I didn't think it was needed, but I can if required.

    Thanks

  2. #2
    Valued Forum Contributor
    Join Date
    06-22-2018
    Location
    Blackpool, England
    MS-Off Ver
    2019
    Posts
    408

    Re: Exclude zero records in results

    Probably would be better to attach a workbook so we can see what your references are referring to.

    Tim
    Never stop learning!
    <--- please consider *-ing !

  3. #3
    Registered User
    Join Date
    04-27-2019
    Location
    Northern Ireland
    MS-Off Ver
    2019
    Posts
    62

    Re: Exclude zero records in results

    No problem
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    06-22-2018
    Location
    Blackpool, England
    MS-Off Ver
    2019
    Posts
    408

    Re: Exclude zero records in results

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    evaluates to 2 because column 2 is the last column that is greater than 0. I presume you want it to include column 5, as that's the first last non-negative number.

    Trouble is, if you change that second bit to read
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Excel interprets all the blank cells as zeros, so this evaluates to 20.

    In order to get round that, we'll look for the last non-blank column using the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and replace the reference to AA11 where it needs to be ">=0" in your original formula with an offset from H11. Then we can change the criteria to ">=0" without it taking the blanks into account.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If this doesn't give you the right results, please can you give a verbal description of what you're trying to achieve. It's done my head in!

    Tim
    Last edited by harrisonland; 04-17-2020 at 11:26 AM.

  5. #5
    Registered User
    Join Date
    04-27-2019
    Location
    Northern Ireland
    MS-Off Ver
    2019
    Posts
    62

    Re: Exclude zero records in results

    Perfect Tim

    That's one heck of a formula.

    Thank you so much.

    Patrick

+ 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. How do you exclude some results on INDEX-MATCH?
    By beerman7 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-19-2019, 11:56 AM
  2. How to exclude rows from Displaying after Hlookup Results
    By EddieMcr in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-05-2015, 06:25 AM
  3. Exclude Records of another Excel Sheet
    By dumiduw in forum Excel General
    Replies: 2
    Last Post: 05-23-2013, 08:39 AM
  4. Replies: 0
    Last Post: 09-11-2012, 04:58 PM
  5. Replies: 0
    Last Post: 03-02-2012, 11:16 AM
  6. vlookup to exclude results.
    By loubear in forum Excel General
    Replies: 2
    Last Post: 10-17-2008, 01:05 PM
  7. [SOLVED] Exclude records that start with *
    By Steve A in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-16-2006, 12:55 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