Aug 09 2020 05:26 AM
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
Aug 09 2020 05:40 AM
SolutionAs an array formula confirmed with Ctrl+Shift+Enter (Windows) or Command+Return (Mac):
=MIN(IFERROR(ABS(B4:B25-50),""))
Aug 09 2020 05:56 AM
Many thanks @Hans Vogelaar for speedy expert response confirmed works!
Aug 09 2020 05:40 AM
SolutionAs an array formula confirmed with Ctrl+Shift+Enter (Windows) or Command+Return (Mac):
=MIN(IFERROR(ABS(B4:B25-50),""))