I am trying to highlight cells in a table that are closest to a target value in each column. I had been using conditional formatting and this formula : =AND(N8<>"",ABS(N8-$E$5)=MIN(ABS(N$8:N$61-$E$5))) for a table that just contained numbers (see screenshot)
table with working conditional formatting to highlight cells
I was wondering if there would be a way to adjust the formula to ignore text values as I would like it apply it to a different table that has text as well as numbers?
Cheers
=AND(ISNUMBER(N8),ABS(N8-$E$5)=MIN((IF(ISNUMBER(N$8:N$61),ABS(N$8:N$61-$E$5)))))