How do I find a column entry closest to a particular value

[feman007]

I'm trying to find the closest value in a column to a specified number, then
list it. Basically, I have a function in which I have to find 25% of the max
value and find which value in the data column that is closest. I was trying
to use a function to search the column but it wasn't working. any thoughts?

Thanks,
Dave

 

[Vasant Nanavati]

Apply conditional formatting to your range (named TheRange) and use the
following condition to show the selected item in a different color:

=ABS(A1-(0.25*MAX(TheRange)))=MIN(ABS(TheRange-(0.25*MAX(TheRange))))

assuming the column starts in A1.

 

[JulieD]

Hi Dave

how many columns are we talking about here?
can they be sorted?
from the following list, what answer would you expect?
........A
1......1
2......3
3......5
4.......8
5......=MAX(A1:A4)*25% which = 2

would you want 1 or 3 returned?
would a VBA solution be acceptable?

Cheers
JulieD