SOLVED

MIN ABS IGNORING #N/A

Copper 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 (Copper 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!

1 best response

Accepted Solutions
best response confirmed by jackdowie (Copper Contributor)
Solution

@jackdowie 

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

 

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

View solution in original post