PDA

View Full Version : Solved: Convert formula to Macro; find closest value and return adjacent values



guatelize
06-13-2012, 05:59 AM
Hello
I have a large file with dates and numbers, I found a formula to extract the closest value and return the adjacent values. But when I injected the formula into my file, it took 1 hour to have a result :whistle: :doh: .
:help Is it possible to speed it up the calculation with a macro or to replace the formula by a macro?
(File included)
Thanks for your help

mikerickson
06-13-2012, 07:09 AM
If you sort column A ascending,
=LOOKUP(E1, $A$1:$A$4000, $A$1:$A$4000) will return the number below the value given.
=LOOKUP(E1, $A$1:$A$4000, $A$2:$A$4001) will return the number above the value given

=LOOKUP(E1,$A$1:$A$4000, $B$1:$B$4000)
=LOOKUP(E1,$A$1:$A$4000, $B$2:$B$4001)

Will return the matching values from column B.

I think this formula will do what you want
=IF((LOOKUP(E1,$A$1:$A$4000, $A$2:$A$4001)-E1)<(E1-LOOKUP(E1,$A$1:$A$4000,$A$1:$A$4000)), LOOKUP(E1,$A$1:$A$4000, $B$2:$B$4001), LOOKUP(E1,$A$1:$A$4000, $B$1:$B$4000))

guatelize
06-14-2012, 01:11 AM
Thank you very much, works very good.