+ Reply to Thread
Results 1 to 5 of 5

Resolve or hide "#NUM!" value return when using the "SMALL" formula

  1. #1
    Registered User
    Join Date
    01-29-2011
    Location
    Upland, California
    MS-Off Ver
    Office 365
    Posts
    32

    Resolve or hide "#NUM!" value return when using the "SMALL" formula

    I am using the "SMALL" formula to return the relative minimum date within a range of cells into a group of three different cells in order to get the three lowest date values. My issue is that when the source range of cells is empty or their is not enough data to populate the destination cells, the destination cells that are not populated display the "#NUM!" error. Is there a way to have the destination cells display as a blank cell when there is no value to return?

    The destination cell formulas are as follows:
    =SMALL($Y9:$AG9,1)
    =SMALL($Y9:$AG9,2)
    =SMALL($Y9:$AG9,3)

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,173

    Re: Resolve or hide "#NUM!" value return when using the "SMALL" formula

    Try =Iferror(SMALL($Y9:$AG9,1),"")

  3. #3
    Registered User
    Join Date
    01-29-2011
    Location
    Upland, California
    MS-Off Ver
    Office 365
    Posts
    32

    Thumbs up Re: Resolve or hide "#NUM!" value return when using the "SMALL" formula

    Thank you! It worked great!

    I'm sorry but I do not see a star icon anywhere on the screen, so I'm adding a thumbs up icon. I had the same issue on another post, where I was not able to indicate that my issue was solved. I hope i can fugure this out!

    Again, thank you very much!

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Resolve or hide "#NUM!" value return when using the "SMALL" formula

    Your profile implies you may require XL2003 compatibility in which case bear in mind that IFERROR is new to XL2007 (and beyond)

    Could you outline where you are placing your various SMALL formulae ? You can use an appropriate range formula (ROWS/COLUMNS) to increment "k" in the SMALL without needing to use a separate constant for each formula.

  5. #5
    Registered User
    Join Date
    01-29-2011
    Location
    Upland, California
    MS-Off Ver
    Office 365
    Posts
    32

    [SOLVED] Resolve or hide "#NUM!" value return when using the "SMALL" formula

    Thank you!

    Can you inform me as to how to mark this thread as solved? I can't seem to find out how to close my threads.

    I think i may have just figured it out. I hope!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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