+ Reply to Thread
Results 1 to 6 of 6

Get Excel to Return a Blank When a Value in My Formula Isnt Found

  1. #1
    Registered User
    Join Date
    09-27-2019
    Location
    Vernon, BC
    MS-Off Ver
    Excel 2010
    Posts
    3

    Get Excel to Return a Blank When a Value in My Formula Isnt Found

    I'm using the following formula to scan a dataset for the minimum temperature recorded for each year. There are some gaps in the data where there is nothing available for a given year, such as 1895, 1910 ect. When I use this forumla excel returns a zero for these instances, but I need to be able to differentiate between a true zero and simply a case where the year was not found. I've tried ISBLANK and I'm still having the same problem.

    =MIN(IF(D:D = 1893 + ROW(A4), IF(E:E = 3,G:G )))

    Any help appreciated!
    Attached Files Attached Files
    Last edited by rachreed; 09-30-2019 at 11:19 AM.

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Get Excel to Return a Blank When a Value in My Formula Isnt Found

    Perhaps:

    =MIN(IF($D$1:$D$10000 = 1893+ROW(A4), IF($E$1:$E$10000 = 3, IF($G$1:$G$10000 <> "", $G$1:$G$10000))))

    Note the switch from column references (D:D) to a limited range ($D$1:$D$10000).

    I presume you know that the formula must be array-entered (press ctrl+shift+Enter instead of just Enter).
    Last edited by joeu2004; 09-27-2019 at 03:54 PM.

  3. #3
    Registered User
    Join Date
    09-27-2019
    Location
    Vernon, BC
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Get Excel to Return a Blank When a Value in My Formula Isnt Found

    Hi there,

    Thanks so much for your response. The formula definitely works to extract the minimum temperatures, but it still appears to be displaying zero even when there is a year missing. Any thoughts?

    Cheers

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Get Excel to Return a Blank When a Value in My Formula Isnt Found

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    09-27-2019
    Location
    Vernon, BC
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Get Excel to Return a Blank When a Value in My Formula Isnt Found

    Thank you

    I have attached a sample workbook with the desired result.

    Appreciate your help.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Get Excel to Return a Blank When a Value in My Formula Isnt Found

    How about something like =IF(COUNTIFS(D:D,year)=0,"n/a",current formula)
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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] If A Cell Within A Range Isnt Blank, Return That Value...
    By Ourkid123uk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-08-2019, 11:17 AM
  2. Excel Formula: Compare lists, return result if not found
    By ironfelix717 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-03-2017, 03:39 PM
  3. Replies: 6
    Last Post: 07-18-2017, 04:51 PM
  4. Code to look at external link and if not found, return error or blank
    By magnus.blomquist in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-05-2016, 06:15 PM
  5. [SOLVED] Return blank if no exact match or blank is found
    By matrixpom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2013, 12:57 AM
  6. [SOLVED] Return data if blank cell is found
    By Dartmouth_jock in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2013, 03:03 PM
  7. have excel return the location of the value found by a MIN formula
    By dstephens80 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-10-2012, 12:28 PM

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