Thread: Find the nearest value from an Array

Hi All,
I need help with the formula which will show the nearest value from an array for an Id. I have attached the sample worksheet.

An agent will login multiple times in a day, i want to know what is the nearest login time.

Thanks in Advance

Regards,
Anto

Assuming D to be your expected results:

D2:
=SUM(IF($A$2:$A$8=$A2,IF(ABS($C$2:$C$8-$B2)=MIN(IF($A$2:$A$8=$A2,ABS($C$2:$C$8-$B2))),$C$2:$C$8)))
confirmed with CTRL + SHIFT + ENTER
copied down

I am sure with more thought it could be condensed.



@bebo021999 - though not relevant to the answer here you could remove the Array in your example:

=LOOKUP(2,1/(A2:A8=A10),D2:D8)
confirmed with Enter

one of the advantages of LOOKUP is that you can generally avoid Array entry by virtue of lookup vector.

The same formula should work - you need simply amend ranges accordingly - though D3 should read 12/27/2011 12:14 AM I think ?

In XL Dates are Integers whereas Time is Decimal (1 = 24 hours, 0.5 = 12 hours etc...).
Given the above you can perform mathematical calculations with dates and time as you would with numbers in general.