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.
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 | |||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | |||
1 | 0:19:42,26 | -0,35 | 0:00:00,00 | 0:00:00,00 | 0:00:00,00 | -0,35 | Example of what I want to do: | ||||||||||||||||
2 | 0:19:42,29 | -0,35 | 0:00:00,03 | 0:00:00,03 | 0:00:00,01 | 0 | |||||||||||||||||
3 | 0:19:42,33 | -0,35 | 0:00:00,07 | 0:00:00,07 | 0:00:00,02 | 0 | Sensor 1 | Sensor 2 | result | ||||||||||||||
4 | 0:19:42,39 | -0,35 | 0:00:00,14 | 0:00:00,14 | 0:00:00,03 | 0 | time | data | time | data | delta | ||||||||||||
5 | 0:19:42,47 | -0,35 | 0:00:00,21 | 0:00:00,21 | 0:00:00,04 | 0 | 0:00:00,00 | 100 | 0:00:00,01 | 102 | ? | ||||||||||||
6 | 0:19:42,53 | -0,35 | 0:00:00,27 | 0:00:00,27 | 0:00:00,05 | 0 | 0:00:00,03 | 105 | 0.00:00:05 | 109 | ? | ||||||||||||
7 | 0:19:42,59 | -0,36 | 0:00:00,33 | 0:00:00,33 | 0:00:00,06 | 0 | 0:00:00,07 | 108 | 0:00:00,08 | 115 | ? | ||||||||||||
8 | 0:19:42,65 | -0,36 | 0:00:00,39 | 0:00:00,39 | 0:00:00,07 | 0 | 0:00:00,14 | 110 | 0:00:00,14 | 125 | ? | ||||||||||||
9 | 0:19:42,71 | -0,36 | 0:00:00,45 | 0:00:00,45 | 0:00:00,08 | 0 | 0:00:00,21 | 120 | 0:00:00,23 | 135 | ? | ||||||||||||
10 | 0:19:42,76 | -0,36 | 0:00:00,51 | 0:00:00,51 | 0:00:00,09 | 0 | |||||||||||||||||
11 | 0:19:42,84 | -0,35 | 0:00:00,59 | 0:00:00,59 | 0:00:00,10 | 0 | |||||||||||||||||
12 | 0:19:42,89 | -0,36 | 0:00:00,64 | 0:00:00,64 | 0:00:00,11 | 0 | idea via interpolation | interpolated value as example | |||||||||||||||
13 | 0:19:42,96 | -0,36 | 0:00:00,70 | 0:00:00,70 | 0:00:00,12 | 0 | |||||||||||||||||
14 | 0:19:43,01 | -0,37 | 0:00:00,75 | 0:00:00,75 | 0:00:00,13 | 0 | 0:00:00,00 | 100 | 0:00:00,00 | 101 | 1 | ||||||||||||
15 | 0:19:43,08 | -0,37 | 0:00:00,82 | 0:00:00,82 | 0:00:00,14 | 0 | 0:00:00,01 | 101,7 | 0:00:00,01 | 102 | 0,333333 | ||||||||||||
16 | 0:19:43,14 | -0,36 | 0:00:00,89 | 0:00:00,89 | 0:00:00,15 | 0 | 0:00:00,02 | 103,3 | 0:00:00,02 | 103,5 | 0,166667 | ||||||||||||
17 | 0:19:43,21 | -0,36 | 0:00:00,95 | 0:00:00,95 | 0:00:00,16 | 0 | 0:00:00,03 | 105 | 0:00:00,03 | 105 | 0 | ||||||||||||
18 | 0:19:43,26 | -0,36 | 0:00:01,01 | 0:00:01,01 | 0:00:00,17 | 0 | 0:00:00,04 | 106,7 | 0:00:00,04 | 106,5 | -0,16667 | ||||||||||||
19 | 0:19:43,33 | -0,36 | 0:00:01,07 | 0:00:01,07 | 0:00:00,18 | 0 | 0:00:00,05 | 108,3 | 0:00:00,05 | 109 | 0,666667 | ||||||||||||
20 | 0:19:43,39 | -0,36 | 0:00:01,13 | 0:00:01,13 | 0:00:00,19 | 0 | |||||||||||||||||
21 | 0:19:43,46 | -0,35 | 0:00:01,20 | 0:00:01,20 | 0:00:00,20 | 0 | and so on | ||||||||||||||||
22 | 0:19:43,51 | -0,36 | 0:00:01,26 | 0:00:01,26 | 0:00:00,21 | 0 | |||||||||||||||||
23 | 0:19:43,58 | -0,36 | 0:00:01,32 | 0:00:01,32 | 0:00:00,22 | 0 | |||||||||||||||||
24 | 0:19:43,64 | -0,36 | 0:00:01,38 | 0:00:01,38 | 0:00:00,23 | 0 | |||||||||||||||||
25 | 0:19:43,71 | -0,35 | 0:00:01,45 | 0:00:01,45 | 0:00:00,24 | 0 | |||||||||||||||||
26 | 0:19:43,77 | -0,35 | 0:00:01,52 | 0:00:01,52 | 0:00:00,25 | 0 | |||||||||||||||||
27 | 0:19:43,82 | -0,35 | 0:00:01,57 | 0:00:01,57 | 0:00:00,26 | 0 | |||||||||||||||||
28 | 0:19:43,89 | -0,35 | 0:00:01,64 | 0:00:01,64 | 0:00:00,27 | 0 | |||||||||||||||||
29 | 0:19:43,95 | -0,35 | 0:00:01,69 | 0:00:01,69 | 0:00:00,28 | 0 | |||||||||||||||||
30 | 0:19:44,00 | -0,35 | 0:00:01,75 | 0:00:01,75 | 0:00:00,29 | 0 | |||||||||||||||||
31 | 0:19:44,07 | -0,36 | 0:00:01,81 | 0:00:01,81 | 0:00:00,30 | 0 | |||||||||||||||||
32 | 0:19:44,14 | -0,36 | 0:00:01,88 | 0:00:01,88 | 0:00:00,31 | 0 | |||||||||||||||||
33 | 0:19:44,20 | -0,36 | 0:00:01,94 | 0:00:01,94 | 0:00:00,32 | 0 | |||||||||||||||||
34 | 0:19:44,27 | -0,37 | 0:00:02,01 | 0:00:02,01 | 0:00:00,33 | 0 | |||||||||||||||||
35 | 0:19:44,33 | -0,38 | 0:00:02,07 | 0:00:02,07 | 0:00:00,34 | 0 | |||||||||||||||||
36 | 0:19:44,39 | -0,38 | 0:00:02,13 | 0:00:02,13 | 0:00:00,35 | 0 | |||||||||||||||||
37 | 0:19:44,45 | -0,39 | 0:00:02,19 | 0:00:02,19 | 0:00:00,36 | 0 | |||||||||||||||||
38 | 0:19:44,51 | -0,39 | 0:00:02,25 | 0:00:02,25 | 0:00:00,37 | 0 | |||||||||||||||||
39 | 0:19:44,57 | -0,38 | 0:00:02,31 | 0:00:02,31 | 0:00:00,38 | 0 | |||||||||||||||||
40 | 0:19:44,63 | -0,38 | 0:00:02,37 | 0:00:02,37 | 0:00:00,39 | 0 | |||||||||||||||||
41 | 0:19:44,69 | -0,39 | 0:00:02,44 | 0:00:02,44 | 0:00:00,40 | 0 | |||||||||||||||||
42 | 0:19:44,76 | -0,39 | 0:00:02,51 | 0:00:02,51 | 0:00:00,41 | 0 | |||||||||||||||||
43 | 0:19:44,82 | -0,39 | 0:00:02,56 | 0:00:02,56 | 0:00:00,42 | 0 | |||||||||||||||||
44 | 0:19:44,89 | -0,39 | 0:00:02,63 | 0:00:02,63 | 0:00:00,43 | 0 | |||||||||||||||||
45 | 0:19:44,95 | -0,38 | 0:00:02,69 | 0:00:02,69 | 0:00:00,44 | 0 | |||||||||||||||||
46 | 0:19:45,00 | -0,38 | 0:00:02,74 | 0:00:02,74 | 0:00:00,45 | 0 | |||||||||||||||||
47 | 0:19:45,07 | -0,38 | 0:00:02,81 | 0:00:02,81 | 0:00:00,46 | 0 | |||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
K1,K3:K47 | K1 | =MROUND(J1,"0:00:00,01") |
J1:J47 | J1 | =D1-D$1 |
T15 | T15 | =T14+(T17-T14)/3 |
T16 | T16 | =T14+(T17-T14)/3*2 |
T18:T19 | T18 | =T17+T17-T16 |
X14:X19 | X14 | =V14-T14 |
O1:O47 | O1 | =XLOOKUP(N1,K$1:K$38,E$1:E$38,0) |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
D1:D506 | Any value | |
E1:E506 | Any value |