Use time as a criteria.... but with +/- 5%

[RockandGrohl]

Hi guys, I'm using the lovely mutli-critiera Index-Match as seen here:

Excel formula: INDEX and MATCH with multiple criteria | Exceljet

In one column I'm matching Date to date, in another column I'm matching currency value to value, and in the third criteria I need to match time.

The problem is that the two systems recorded time with slightly different values, so 05:12:24 on one is 05:13:43 on the other - they never appear to be more than 3 minutes out.

In this particular set of data, it's very, very rare that two users will have the same transaction value on the same date and within the same timeframe (3 mins or so), so I'd like the index-match to positively match someone where the date, value and time matches, but within +/- 5% of the time. (which should be 3 minutes, right?)


Thanks!

 

[RockandGrohl]

Bump

 

[AlanY]

would something like this work?

Find closest match

To find the closest match in numeric data, you can use the XLOOKUP function or an INDEX and MATCH formula. In the example shown, the formula in F5 is: =XLOOKUP(0,ABS(C5:C16-E5),B5:B16,,1) The result is "Tokyo" because the cost of $1195 is closest to the target value in cell E5, $1200.

exceljet.net

 

[RockandGrohl]

would something like this work?

Find closest match

To find the closest match in numeric data, you can use the XLOOKUP function or an INDEX and MATCH formula. In the example shown, the formula in F5 is: =XLOOKUP(0,ABS(C5:C16-E5),B5:B16,,1) The result is "Tokyo" because the cost of $1195 is closest to the target value in cell E5, $1200.

exceljet.net

I'm looking for something I can integrate into the existing formula without additional helpers, so:

{=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(>=H7*0.95=D5:D11)*(<=H7*1.05=D5:D11),0))}

H5 = Date
H6 = Value
H7 = Time

But really not sure if that would work.

 

[Phuoc]

Try this

{=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(D5:D11>=H7*0.95)*(D5:D11<=H7*1.05),0))}

 

[RockandGrohl]

Try this

{=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(D5:D11>=H7*0.95)*(D5:D11<=H7*1.05),0))}

That looks like it should work, just checking though, does this formula allow for swapping the order or matching? (First brackets are criteria = range, last ones are range = criteria)?

 

[Phuoc]

No problem, you can change it. The results are the same.

{=INDEX(E5:E11,MATCH(1,(B5:B11=H5)*(C5:C11=H6)*(D5:D11>=H7*0.95)*(D5:D11<=H7*1.05),0))}

 

[RockandGrohl]

Oh nice - I'll try this when I log on later, but it looks like it should work nicely. Thank you!

 

[RockandGrohl]

No problem, you can change it. The results are the same.

{=INDEX(E5:E11,MATCH(1,(B5:B11=H5)*(C5:C11=H6)*(D5:D11>=H7*0.95)*(D5:D11<=H7*1.05),0))}

This appears to work, although my numbers were way off. When you multiply a time like 15:03:25 by 0.95 you get 14:18:15 - which is nearly an hour of variance.

Instead you need to multiply by something larger - 0.998 seems to give a result of 15:01:37, so it's about a couple minutes leeway to an earlier time, and then you multiply by 1.002 to get a couple minutes leeway on the other side.

The new problem is that this is applicable for 15 hours into the day, where the number is 0.63. So this will have a more dramatic affect the later in the day you get. As an example, the above calculations (0.998 and 1.002) give 00:01:48 seconds earlier forgiveness and 00:01:48 seconds later forgiveness when you calculate against 15:03:25.

If you calculate against 22:10:10, you get 22:07:30 and 22:12:50 - which is 00:02:40 variance either side.

And likewise if you calculate against 4 minutes past midnight, there's no change.


So the solution appears to be to find out what one minute is in decimal value - 0.00069444, then multiply it by two to get 0.0013889, and then say I'm looking for this figure >= time - 0.0014, or <= time + 0.0014 (rounded up for simplicity)

This works and results in a positive match. If my target time is 15:03:25, then anything between 15:01:26 and 15:05:25 results in a match. If I go above or below, it's curtains.

 

[RockandGrohl]

No problem, you can change it. The results are the same.

{=INDEX(E5:E11,MATCH(1,(B5:B11=H5)*(C5:C11=H6)*(D5:D11>=H7*0.95)*(D5:D11<=H7*1.05),0))}

Ok Another problem, it's not fully solved.

Looking at my above solution, when you are close to midnight it doesn't roll back or forward a day as appropriate, so now the best solution is to combine date and time and search on that criteria instead. Will require so fettling of my sheets.