Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
error when blank - please help
Good morning,
When $N$16:$N$25 in the formula below contains blanks, it returns #NA How can i modify this formula to ignore blanks? {=INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25, IF(ISERROR(MATCH(-MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25, 0)),MATCH(MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25, 0),MATCH(-MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25, 0)))} The formula is finding the # closest to 0 (negatives first) should 0 not exist and retunring the corresponding rate in column A. Entering a number into the blank fields is not an option as I have 20 sheets with many tables. Thank you in advance for your help! Jeze |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Leaving a cell blank when there is an error message - HELP | Excel Discussion (Misc queries) | |||
#DIV/0! Error - Need to display 0 or blank | Excel Worksheet Functions | |||
Leave Cell Blank if value is an error, below 0 or above 80 | Excel Discussion (Misc queries) | |||
Mystery: System Error and blank squares | Excel Discussion (Misc queries) | |||
How to display a blank cell with no error message | Excel Worksheet Functions |