Forum Discussion
Solved
MIN ABS IGNORING #N/A
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
As an array formula confirmed with Ctrl+Shift+Enter (Windows) or Command+Return (Mac):
=MIN(IFERROR(ABS(B4:B25-50),""))
2 Replies
As an array formula confirmed with Ctrl+Shift+Enter (Windows) or Command+Return (Mac):
=MIN(IFERROR(ABS(B4:B25-50),""))
Many thanks HansVogelaar for speedy expert response confirmed works!
