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...
As an array formula confirmed with Ctrl+Shift+Enter (Windows) or Command+Return (Mac):
=MIN(IFERROR(ABS(B4:B25-50),""))
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!