Please find below my problem statement:
Requirement : For every row in the below displayed records, I want to find the records within 10 minutes range and within +-15 units of the quantity. And then find the record which is closest in time with the current record and fetch the Price value.
Example : Say for the first record with Sno=1,the records which fall within 10 min time range and within +-15 units of quantity are records with Sno = 2,4,6. Of these three records the one closest in time range is record Sno=2.So I want to get the Price value for record with Sno=2 which is 12.
- Sno Price Quantity Time
- 1 10 100 05/08/2013 10:12:13 AM
- 2 12 111 05/08/2013 10:10:11 AM
- 3 13 123 05/08/2013 10:22:13 AM
- 4 2 111 05/08/2013 10:22:13 AM
- 5 13 112 05/08/2013 10:42:13 AM
- 6 14 100 05/08/2013 10:15:13 AM
Appreciate your help :)
Thanks
RVK
Assuming data in A2:D7, enter in E2 and fill down:
=LOOKUP(2,1/(ABS(D$2:D$7-D2)<TIME(,10,1))/FREQUENCY(0,ABS(D$2:D$7-D2)*(2*(ABS(C$2:C$7-C2)<=15)-1)-(A$2:A$7=A2)),B$2:B$7)
Explanation
The formula can be broken down into several components (by highlighting the chosen section in the formula bar and pressing F9):
A: =(ABS(D$2:D$7-D2)<TIME(0,10,1)) returns {TRUE;TRUE;TRUE;TRUE;FALSE;TRUE} which is an array indicating if it is within 10 minutes (or 10:01 to account for rounding)
B: =FREQUENCY(0,{...}) returns {0;1;0;0;0;0;0} which is the closest match in time within 15 units of quantity. ABS(D$2:D$7-D2)*(2*(ABS(C$2:C$7-C2)<=15)-1) returns an array of time differences which are positive if the product is within 15 units and negative otherwise, -(A$2:A$7=A2) is added so a negative number is returned for the current row. With zero in the first argument, the frequency function will ignore these negative numbers.
C: =LOOKUP(2,1/A/B,B$2:B$7) returns the corresponding match where A and B are both 1 or TRUE. The reciprocal is used as LOOKUP ignores errors, if no match is found LOOKUP will return #N/A.
