+ Reply to Thread
Results 1 to 3 of 3

INDEX MATCH formula, ignore blank cells and define time frames

  1. #1
    Registered User
    Join Date
    02-15-2016
    Location
    England
    MS-Off Ver
    MS Office 2016 Home and Student
    Posts
    2

    INDEX MATCH formula, ignore blank cells and define time frames

    Hi!

    I've read a few earlier posts to try and overcome the issue I'm having but with no success, so I'm a new member here.

    I'm attempting to create a template for recorded data that's relevant to quality improvement. I've thus far managed to define some of the queries to be explored i.e., first data value for each row, whether that value was within a threshold, whether a value within the row's data range exceeded a threshold; however, I'm seriously struggling to define the values for the remaining queries I have (shown on the far right of the attachment)

    What I'm hoping to receive help on is identifying whether a range fell below a value (unfortunately I've numerous errors trying to achieve this, especially as there are blank cells which need to be ignored but I'm uncertain how to include this in the formula; I've essentially attempted to use the opposite of the formula for identifying whether values within a range exceeded a threshold). Following this I need to determine whether it's possible to include time-frames (i.e. >2 days or >3 days) with these formulas too, adding further specificity to the analysis?

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,410

    Re: INDEX MATCH formula, ignore blank cells and define time frames

    I think this article will shed some light on why the exceed threshold works but the below threshold doesn't work.
    http://www.utteraccess.com/wiki/inde...Array_Formulas

    There is a section in the article about weeding out "logical zeros."
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    02-15-2016
    Location
    England
    MS-Off Ver
    MS Office 2016 Home and Student
    Posts
    2

    Re: INDEX MATCH formula, ignore blank cells and define time frames

    Thanks!

    I've actually reverted to simply using a COUNTIF function for the thresholds. It's a little more convoluted I suppose but it works. Unfortunately, my greatest area of concern is identifying whether changes occurred after 48hours (2 consecutive cells) or 3 days (3 consecutive cells) - any advice?

+ 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. Index Match Function? Ignore blank rows - (rows without values)
    By jgray in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-26-2015, 01:12 PM
  2. [SOLVED] INDEX/MATCH formula doesn't like blank cells.
    By Big.Moe in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-07-2015, 01:40 PM
  3. How to ignore blanks in an Index/Match Array Formula
    By michaelcarrera in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-06-2015, 08:50 PM
  4. Replies: 0
    Last Post: 11-05-2014, 10:54 AM
  5. [SOLVED] how to ignore text in a sumproduct, index, and match formula.
    By sbham in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-12-2013, 04:55 PM
  6. [SOLVED] An INDEX MATCH formula has blank cell - need to ignore or delete
    By rls231 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-26-2013, 11:13 PM
  7. Ignore blank cells in time duration formula
    By bigjez in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-30-2012, 01:10 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