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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I find the cell/data the closest/nearest to another data? 8
Closest value lookup? 11
Function to Return a Date Value Q 4
formula for a # in a column of #'s that's closest to a certain val 10
Finding Zeros 2
this is killing me. INDEX/MATCH "closest to X" in a smart way? 5
Double Match (one with less than or equal to) 0
How to determine the value? 8

Back
Top