Need an Excel Wizard

[TubsAlwaysWins]

I need some help making an Excel function. Im struggling to explain what I want to ChatGPT (Windows CoPilot). 

Im trying to Interpolate (I think thats the right word) data ive pasted in Column Z to match the length of data columns A > Y. The data in column Z is from an Wideband o2 sensor and was recorded via Putty over serial and pasted into the csv file, while the data in A > Y was recorded by my engine tuner chip. Both sets of data were recorded over the same time span (In this case, ~ 35 seconds). Im trying to get excel to basically spread the data in Z out across all the rows that the tuner has logged to. (So 364 rows of data needs to be spread across 1005 rows in this case). I need this function to be flexible so regardless of how many rows of data the tuner and the o2 sensor record, I can spread them out and make them (roughly) line up, and have excel fill in the blanks. 

The closest formula ive gotten working so far is =IFERROR(INDEX($Z$2:$Z$1000, MATCH(AA1, $A$2:$A$1000, 1)), "") , which fills in every other row with a 0 and is making every other row besides the zero have a value of 14.6. Its the closest to what I want it to do, although its far off.

To make things more difficult, the o2 sensor does not log a time stamp. Im just starting and stopping logs at the same time. 

 

Hopefully that makes sense. 

 

'datalog1.csv' is the original log from the tuner. 

'datalog1.txt' is the logged data from the o2 sensor.

'datalog1.xlsx' is a sheet with datalog1.txt pasted into column Z. 

 

TL:DR is How do I make data I past in Column Z (Rows 2 thru X) spread across column AA (Rows 2 thru X), while being flexible enough that either log can be whatever length and I can interpolate (Please correct me if im using the wrong word) the data and line it up. 

datalog1.csv datalog1.txt datalog1.xlsx

[Monkey Dust]

To confirm, you want the values in Column Z spread out evenly over all the rows, columns A -Y fill?

[Avocado Diaboli]

The problem here is that any interpolation is just pure guess work. If you have no meaningful way of syncing up the readouts, you're not gonna get a lot of useful information out of this.

 

I mean, look at the actual time-stamped data in your CSV and the wild swings in delay between each readout.

 

If you can be sure that the data in your text file is a readout of perfectly spaced intervals, you'd have to also take the fluctuations of the CSV file into account when calculating an interpolated value. But since you don't even have time stamps for that data, you're only compounding inaccuracies here.

[TubsAlwaysWins]

1 hour ago, Monkey Dust said:

To confirm, you want the values in Column Z spread out evenly over all the rows, columns A -Y fill?

Yes I think? 

I need the data in A - Y to stay where it is but need the data in Z to stretch the same span as the data in A-Y. 

Hopefully that makes sense

[TubsAlwaysWins]

26 minutes ago, Avocado Diaboli said:

The problem here is that any interpolation is just pure guess work. If you have no meaningful way of syncing up the readouts, you're not gonna get a lot of useful information out of this.

 

I mean, look at the actual time-stamped data in your CSV and the wild swings in delay between each readout.

 

If you can be sure that the data in your text file is a readout of perfectly spaced intervals, you'd have to also take the fluctuations of the CSV file into account when calculating an interpolated value. But since you don't even have time stamps for that data, you're only compounding inaccuracies here.

Yeah I know its not gonna be super arcuate. Just trying to spread it out and have excel fill in the gaps. 

[Monkey Dust]

Here's my solution. It relies on the assumption that the Wideband O2 data is evenly spaced, and covers the same time period as the data from the tuner chip.

I've moved the Wideband O2 data to its own tab, and converted both sets of data into Tables. Converting to Tables isn't essential, but it will make life easier when you come to paste new data in. The formula in column Z calculated the fraction of the total time elapsed and pulls the closest corresponding Wideband O2 data.

datalog1.xlsx