Plotting U,Z variables from X,U and X,Z experimental data tables

Please Login or Register  to view this content.

I added an interpolation function, so it's an xlsm file now.

What is needed is a new table with values for u and z at the same values for x. The hardest part of a problem like this is going to be deciding how best to obtain values for u and z at an arbitrary x. I use 2 general approaches:

1) curve fitting/regression: If I can readily identify a function that will fit my data, I will regress a function that I can then use to obtain u and z at any value for x. In this case, I don't see an obvious simple function that would fit the data.
2) linear interpolation: Linear interpolation can be a simple method to obtain "intermediate" values from a table of values like you have. Unfortunately, Excel does not have a built in linear interpolation function (Quattro Pro does, and other spreadsheets might, if you are not bound to Excel). If you are bound to Excel, and you choose to use linear interpolation, you will have to come up with an algorithm for it. We discuss it many times on this forum, and I've even seen a few UDF's for it (such as the one included in shg's file), so you might search this forum carefully if you are not sure how to program linear interpolation.

In setting up the table, I would probably pick one of the two that you have, and add a third column to that table. The table might look something like:

Please Login or Register  to view this content.

where the "linterp" function is whatever you end up using for the "z at arbitrary x" function. Once this table is in place, it should be easy to plot column B vs. column C in a scatter plot.

1. It's a user-defined function I added to the workbook. You have to enable macros for it to work.

2. It is a linear intepolation between adjacent points.

I thought firstly that the best way to make this is to make a point-to-point linear regression from one of the tables and adjust time to a single counter and I think that this is not possible in Excel, right?

It is most certainly possible in Excel, but Excel does not have a built in function for it. shg's UDF should do exactly what you want (to one decimal place, he gets the same thing my own LINTERP() UDF outputs).

to one decimal place, he gets the same thing my own LINTERP()

One significant digit is all we share?