SOLVED

MIN ABS IGNORING #N/A

%3CLINGO-SUB%20id%3D%22lingo-sub-1576278%22%20slang%3D%22en-US%22%3EMIN%20ABS%20IGNORING%20%23N%2FA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576278%22%20slang%3D%22en-US%22%3E%3CP%3EHI%20all%3C%2FP%3E%3CP%3EI%20would%20like%20to%20identify%20the%20closest%20value%20to%20a%20specified%20value%20ignoring%26nbsp%3B%23N%2FA%3C%2FP%3E%3CP%3Eeg%20closest%20to%2050%20in%20attachment.%26nbsp%3B%3CBR%20%2F%3EMIN(ABS(B4%3AB25-(50)))%20correctly%20returns%26nbsp%3B%23N%2FA%20not%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%2287%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2287%22%20height%3D%2221%22%3E1.18510094%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Eas%20desired%3C%2FP%3E%3CP%3EWhere%20the%26nbsp%3B%23N%2FAs%20start%20appearing%20in%20the%20column%20is%20determined%20elsewhere%20and%20I%20want%20them%20to%20continue%20to%20appear%2C%20so%20the%20question%20is%20how%20to%20ignore%20them%3C%2FP%3E%3CP%3EHelp%20much%20appreciated.%3C%2FP%3E%3CP%3Ejack%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1576278%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1576301%22%20slang%3D%22en-US%22%3ERe%3A%20MIN%20ABS%20IGNORING%20%23N%2FA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576301%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F753813%22%20target%3D%22_blank%22%3E%40jackdowie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20an%20array%20formula%20confirmed%20with%20Ctrl%2BShift%2BEnter%20(Windows)%20or%20Command%2BReturn%20(Mac)%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DMIN(IFERROR(ABS(B4%3AB25-50)%2C%22%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1576314%22%20slang%3D%22en-US%22%3ERe%3A%20MIN%20ABS%20IGNORING%20%23N%2FA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576314%22%20slang%3D%22en-US%22%3E%3CP%3EMany%20thanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bfor%20speedy%20expert%20response%20confirmed%20works!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

HI all

I would like to identify the closest value to a specified value ignoring #N/A

eg closest to 50 in attachment. 
MIN(ABS(B4:B25-(50))) correctly returns #N/A not

1.18510094

as desired

Where the #N/As start appearing in the column is determined elsewhere and I want them to continue to appear, so the question is how to ignore them

Help much appreciated.

jack

2 Replies
Best Response confirmed by jackdowie (Occasional Contributor)
Solution

@jackdowie 

As an array formula confirmed with Ctrl+Shift+Enter (Windows) or Command+Return (Mac):

 

=MIN(IFERROR(ABS(B4:B25-50),""))

Many thanks @Hans Vogelaar for speedy expert response confirmed works!