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

**ascottbag** · 12-26-2011, 03:39 PM

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)

**JieJenn** · 12-26-2011, 03:54 PM

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

**ascottbag** · 12-26-2011, 04:18 PM

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!

**DonkeyOte** · 12-26-2011, 04:35 PM

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.

**ascottbag** · 12-26-2011, 05:09 PM

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!