Looking for Lookup Formula Based On Nearest Number

[Jhon Mustofa]

hi all...

i have problem about how to lookup data based on nearest number or round number
with criteria <.50 --rounddown or >=.50 --roundup

for easy, please check my attachment file..

for anyhelp help me, greatly appreciated..

john m

[ChemistB]

How do you determine that 101.0 (cell B10) should be in Cluster 1.2 and not 1.1?

[Jhon Mustofa]

hi...

cause 100.50 ---with decimal .50 so roundup to or nearest to 101.00
criteria: if decimal <.50 ---rounddown or if decimal >=.50 ----roundup

[XLent]

If i've understood what you're trying to do - I would be inclined to store the co-ordinates of the closest match, once, (having adjusted model values per rounding requirement), and then use the co-ordinates to return the cluster & model values, e.g.:

Please Login or Register  to view this content.

per your sample this would generate 1.1 / Best, 1.2 / Best, 1.2 / Better, 1.2 / Worst ... and if you added say 168 as new row that would generate 2.1 / Better

[Bo_Ry]

Please try at
C9
=LOOKUP(1,1/MMULT(--(MIN(ABS(B9-$B$3:$D$5))=ABS(B9-$B$3:$D$5)),TRANSPOSE(COLUMN($B$3:$D$5))^0),$A$3:$A$5)

D9
=LOOKUP(1,1/MMULT(TRANSPOSE(ROW($B$3:$D$5))^0,--(MIN(ABS(B9-$B$3:$D$5))=ABS(B9-$B$3:$D$5))),$B$2:$D$2)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Jhon Mustofa]

thank guys..work perfect!!!