XLOOKUP not working with time (0:00:00,00 format) / Interpolating Values

S

Sledgar

New Member
Joined
Jan 31, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hey there

I'm currently working on results a sensor gave me via the data streamer. This gives out Coolums D and E which is the time and the data of the sensor. Later on I will have 2 sensors which I want to compare. So I will have 2 sets of 2 collums each. The comparison is simply the result of sensor 1 minus the result of sensor 2. The problem is that as you can see the timestamps are not consistent. So sensor one might give out data at 0:19:42,26 and Sensor 2 maybe at 0:19:42,27. My idea to fix this was to create collum J in which the timestamps simly start at 0 ( simply to make it look better), then create a new collum N wich starts at 0:00:00,00 and has 0:00:00,01 next and so on. Via XLOOKUP I wanted to assign the data of my Sensor to each timestamp, which would result in some timestamps like 0:00:00,03 having data and 0:00:00,04 not having data for example. After that I wanted to interpolate between the existing data values. I hope this does make sense. I would be very happy if someone of you would have a better idea I'd be happy as hell.

Here comes the problem: The XLOOKUP function does not wotk on timestamps other than 0. Obviously I spend some time googling the issue and tested various things. Both cells (for example K2 and N4 containing 0:00:00,03) are the exact same. comparing both cells gives out true and I even rounded the original value J2 since it contained an additonal number (beeing format 0:00:00,000). No matter what I do I cant make it work. Id be very very glad If someone could help me out

Ive included my sheet as well as a little explanation on the side.

Book1
DEFGHIJKLMNOPQRSTUVWX
10:19:42,26-0,350:00:00,000:00:00,000:00:00,00-0,35Example of what I want to do:
20:19:42,29-0,350:00:00,030:00:00,030:00:00,010
30:19:42,33-0,350:00:00,070:00:00,070:00:00,020Sensor 1Sensor 2result
40:19:42,39-0,350:00:00,140:00:00,140:00:00,030timedatatimedatadelta
50:19:42,47-0,350:00:00,210:00:00,210:00:00,0400:00:00,001000:00:00,01102?
60:19:42,53-0,350:00:00,270:00:00,270:00:00,0500:00:00,031050.00:00:05109?
70:19:42,59-0,360:00:00,330:00:00,330:00:00,0600:00:00,071080:00:00,08115?
80:19:42,65-0,360:00:00,390:00:00,390:00:00,0700:00:00,141100:00:00,14125?
90:19:42,71-0,360:00:00,450:00:00,450:00:00,0800:00:00,211200:00:00,23135?
100:19:42,76-0,360:00:00,510:00:00,510:00:00,090
110:19:42,84-0,350:00:00,590:00:00,590:00:00,100
120:19:42,89-0,360:00:00,640:00:00,640:00:00,110idea via interpolationinterpolated value as example
130:19:42,96-0,360:00:00,700:00:00,700:00:00,120
140:19:43,01-0,370:00:00,750:00:00,750:00:00,1300:00:00,001000:00:00,001011
150:19:43,08-0,370:00:00,820:00:00,820:00:00,1400:00:00,01101,70:00:00,011020,333333
160:19:43,14-0,360:00:00,890:00:00,890:00:00,1500:00:00,02103,30:00:00,02103,50,166667
170:19:43,21-0,360:00:00,950:00:00,950:00:00,1600:00:00,031050:00:00,031050
180:19:43,26-0,360:00:01,010:00:01,010:00:00,1700:00:00,04106,70:00:00,04106,5-0,16667
190:19:43,33-0,360:00:01,070:00:01,070:00:00,1800:00:00,05108,30:00:00,051090,666667
200:19:43,39-0,360:00:01,130:00:01,130:00:00,190
210:19:43,46-0,350:00:01,200:00:01,200:00:00,200and so on
220:19:43,51-0,360:00:01,260:00:01,260:00:00,210
230:19:43,58-0,360:00:01,320:00:01,320:00:00,220
240:19:43,64-0,360:00:01,380:00:01,380:00:00,230
250:19:43,71-0,350:00:01,450:00:01,450:00:00,240
260:19:43,77-0,350:00:01,520:00:01,520:00:00,250
270:19:43,82-0,350:00:01,570:00:01,570:00:00,260
280:19:43,89-0,350:00:01,640:00:01,640:00:00,270
290:19:43,95-0,350:00:01,690:00:01,690:00:00,280
300:19:44,00-0,350:00:01,750:00:01,750:00:00,290
310:19:44,07-0,360:00:01,810:00:01,810:00:00,300
320:19:44,14-0,360:00:01,880:00:01,880:00:00,310
330:19:44,20-0,360:00:01,940:00:01,940:00:00,320
340:19:44,27-0,370:00:02,010:00:02,010:00:00,330
350:19:44,33-0,380:00:02,070:00:02,070:00:00,340
360:19:44,39-0,380:00:02,130:00:02,130:00:00,350
370:19:44,45-0,390:00:02,190:00:02,190:00:00,360
380:19:44,51-0,390:00:02,250:00:02,250:00:00,370
390:19:44,57-0,380:00:02,310:00:02,310:00:00,380
400:19:44,63-0,380:00:02,370:00:02,370:00:00,390
410:19:44,69-0,390:00:02,440:00:02,440:00:00,400
420:19:44,76-0,390:00:02,510:00:02,510:00:00,410
430:19:44,82-0,390:00:02,560:00:02,560:00:00,420
440:19:44,89-0,390:00:02,630:00:02,630:00:00,430
450:19:44,95-0,380:00:02,690:00:02,690:00:00,440
460:19:45,00-0,380:00:02,740:00:02,740:00:00,450
470:19:45,07-0,380:00:02,810:00:02,810:00:00,460
Sheet1
Cell Formulas
RangeFormula
K1,K3:K47K1=MROUND(J1,"0:00:00,01")
J1:J47J1=D1-D$1
T15T15=T14+(T17-T14)/3
T16T16=T14+(T17-T14)/3*2
T18:T19T18=T17+T17-T16
X14:X19X14=V14-T14
O1:O47O1=XLOOKUP(N1,K$1:K$38,E$1:E$38,0)
Cells with Data Validation
CellAllowCriteria
D1:D506Any value
E1:E506Any value
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
I want to help you, the XL2BB method does not provide sufficient precision for this kind of problem. If the following does not resolve your problem, upload an example Excel file (redacted) that demonstrates the problems to a file-sharing website, and post the download URL in a response here. I like box.net/files; others like dropbox.com. In any case, test the download URL first, being careful to log out of all websites that share the same login, in order to ensure that anyone else can access the file anonymously.

In the uploaded Excel file, please do __not__ replace formulas with constants, as it seems you might have done for the XL2BB copy.

If you must substitute constants to redact the file or to avoid external references, please show us what the original formula looked like, either in your posting here or (better) as a comment or textbox in the Excel file.


Sledgar said:
Both cells (for example K2 and N4 containing 0:00:00,03) are the exact same
Click to expand...

Probably not. They might appear to be the same due to limitations of Excel formatting. But XLOOKUP compares their exact binary values, which might differ infinitesimally.

To confirm, enter the formula =SUM(K2, -N4) formatted as Scientific. 0.00E+00 indicates that they are exact binary match. Something like 1.11E-16 indicates an infinitesimal difference.

Note: Use the SUM formula, not =K2-N4. The latter might result in exactly zero (0.00E+00) due to tricks that Excel plays (sigh).


Sledgar said:
comparing both cells gives out true
Click to expand...

Similarly, =K2=N4 might return TRUE due to tricks that Excel plays (sigh). Instead, use =ISNUMBER(MATCH(K2, N4, 0)).


Sledgar said:
I even rounded the original value J2 since it contained an additonal number (beeing format 0:00:00,000)
Click to expand...

MROUND(..., "0:0:0.01") is not a reliable way to round to the decisecond (10/1000 sec).

Use --TEXT(..., "[h]:m:s.00") instead.

Note: You might write "[h]:m:s,00" because you use comma for the decimal point.

To demonstrate:

Book1
ABCD
30:00:00.032
40:00:00.030FALSE
50:00:00.030TRUE
60:00:00.030
Sheet1


Select a cell or hover the cursor over it to see formulas.

A3 contains the constant 0:0:0.032, and A6 contains the constant 0:0:0.03.

Both A4 (MROUND) and A5 (TEXT) __appear__ to be 0:0:0.03. But B4 is FALSE, which indicates they are not a binary match. In contrast, B5 is TRUE.

-----

Changing =XLOOKUP(N1,K$1:K$38,E$1:E$38,0) to =XLOOKUP(N1,K$1:K$38,E$1:E$38,0,1) __seems__ to remedy the problem for your posted examples. That is, change the match type to "approximate match" (1).

Note that unlike VLOOKUP and MATCH, "exact match" (0) is the default for XLOOKUP.

But that might be an illusion caused by the imperfect XL2BB method. Or it might not be a reliable solution for you, in general.

At the very least, note that XLOOKUP(...,1) effectively looks up the __truncated__ time, not the __rounded__ time.

As a __guess__, the following change might work for you. Change the formula in K1 to =--TEXT(D1-D$1, "[h]:m:s.00"). And that obviates the need for column J.

Again, remember that I use period for the decimal point. It appears that you use comma.


-----
PS.... I did not look at the columns under "what I want to do". There might be additional work to do there. LMK if the solutions above fix any problems with the design in columns S:X.
 
Last edited:
Upvote 0
Embarrassing "brain fart"....

joeu2004 said:
MROUND(..., "0:0:0.01") is not a reliable way to round to the decisecond (10/1000 sec)
Click to expand...

It's not even a reliable way to round to the centisecond ;) , which 10/1000 truly is. (Yeah, I know: I shoulda just written one-hundredth. Klunk!)


joeu2004 said:
I want to help you, the XL2BB method does not provide sufficient precision for this kind of problem
Click to expand...

Since I'm correcting the "big" mistake above, I might as well correct this one. It should read: ``but the XL2BB method ...``.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top