Linear interpolation is a common tool for approximating data between two close points (very close), otherwise it would not be linear. Interpolation is not complicated if you perform it with a hand calculator or in a spreadsheet once or twice.
Repeated calculations, despite being easy to perform, become tedious and could be a source of error since data points need to be picked from a table.
In this post, Google Sheets is used to automate the linear interpolation between two points in a table. For this purpose, a user defined function is created.
CONTENTS
1 A short comment on the procedure
1 A short comment on the procedure
As you may imagine, the user defined function can be a little complex since it not only has to perform the linear interpolation but to look for the two suitable points to be used.
In order to give a concise idea, consider the case of the unknown density of water at certain temperature. Tables of temperature and density of water are available so that the calculation, by linear interpolation, is possible among the tabulated data.
 |
| Fig. 01 Schematics showing the input data at $T_1$ and $T_3$ for the calculation of density at $T_2$. |
The idea of this user defined function is such that,
- it should start working with only one input value, say the temperature $T_2$,
- starting with $T_2$ it should look in the column of temperatures for exactly the temperature above (or below) it, say $T_1$. In other words, looked temperature should exist among those in table,
- once $T_1$ is found, another temperature is required. The function should look for the temperature, among those in the table, just below (or above) it, say $T_3$. In this way, $T_1<T_2<T_3$,
- recognizing that density data is available for $T_1$ and $T_3$ a simple linear interpolation can be readily performed by the equation of a straight line.
Notice that the nature of the data in a table is unknown: these can be integers or real numbers. You will just looking for available numbers in a list. This is way we just say the temperature above and below $T_2$ since built-in functions of Google Sheets can not always work for this purpuse.
2 Building the user defined function
This function was built thinking first on how to locate $T_1$ and from it $T_3$ since this was the tricky part. Also, the linear interpolation calculation is simple as a matter of fact.
Some restrictions are to be considered if we want to built a function requiring only $T_2$ as input: the table needs to be always placed in the same place in every sheet you take the function to. If not, error will occur since data will not be in the place the function thinks it is.
In fact, the reader of this post may improve the present function by allowing it to search data in a provided sheet cells range. As mentioned before, this function is based on simple linear interpolation and therefore in the idea of slope,
$m=\dfrac{y_3-y_1}{x_3-x_1}$ Eq. (01)
where the points $(x_1,y_1)$ and $(x_3,y_3)$ need to be known so that $m$ can be calculated.Now, if you need to kown the value of $y_2$ corresponding to a set $x_2$ and the point $(x_2,y_2)$ lines, approximately, on the straight line joining the first two points the same slope $m$ must hold.
You may then, write the following expression as well,
$m=\dfrac{y_2-y_1}{x_2-x_1}$ Eq. (02)
and isolating $y_2$ from Eq. (02) the following is obtained,
$y_2=m\left( x_2-x_1 \right)+y_1$ Eq. (03)
Next, since what we are looking for is a single formula to pass or translate into a single command in Google Sheets all we need to do is to combine Eqs. (01,03),
Let us now introduce the final user defined function,
=(LOOKUP(indirect(address(MATCH(cell,DensityData!$B$4:$B$280,1)+4,2,1,true,"Densitydata")),DensityData!$B$4:$B$280,DensityData!$C$4:$C$280)-LOOKUP(indirect(address(MATCH(cell,DensityData!$B$4:$B$280,1)+3,2,1,true,"Densitydata")),DensityData!$B$4:$B$282,DensityData!$C$4:$C$282))/(indirect(address(MATCH(cell,DensityData!$B$4:$B$280,1)+4,2,1,true,"Densitydata"))-indirect(address(MATCH(cell,DensityData!$B$4:$B$280,1)+3,2,1,true,"Densitydata")))*(cell-indirect(address(MATCH(cell,DensityData!$B$4:$B$280,1)+3,2,1,true,"Densitydata")))+LOOKUP(indirect(address(MATCH(cell,DensityData!$B$4:$B$280,1)+3,2,1,true,"Densitydata")),DensityData!$B$4:$B$282,DensityData!$C$4:$C$282)
It is not be easy to explain, in detail, how the above function was created since this is the final result. For now, all you need to know is that before taking the above function into your Google Sheet document some simplifications need to be made.
First, you will need a sheet named: Densitydata. In this sheet you should have your data table. As the function is executed it will locate the sheet Dentsitydata and in it the table with your data.
Second, your data table should start at cell B4 and end at cell C282. This give you plenty of room to manage looking for data in very large tables. In our case of temperature and density, temperature is located in the left column while density in the right column, just as shown below.
 |
| Fig. 02 Extract of a temperature vs density for water data in Google Sheets. Notice that the data starts in B4 but since only few points are available it does not reach row 282. |
It does not matter if you not have enough data to fill until row 282. If you no data is the cell, Google Sheets will not look in those rows.
In a part of the function there is a component that looks for a suitable $T_1$ based on a supplied $T_2$. The temperature $T_1$ should be the inmediate data above $T_2$. This part of the function is,
indirect(address(MATCH(cell,DensityData!$B$4:$B$280,1)+3,2,1,true,"Densitydata"))You can run this part of the function by passing a value into cell as follows
=indirect(address(MATCH(63,DensityData!$B$4:$B$280,1)+3,2,1,true,"Densitydata"))whose output would be 60. The other key part of the fuction is that allowing for finding $T_3$ or the number just below $T_2=63$ in this example. This is,
indirect(address(MATCH(cell,DensityData!$B$4:$B$280,1)+4,2,1,true,"Densitydata"))
Next, with these two key instructions one may look in column C for the corresponding densities and built the user defined function to evaluateEq. (04).
With this in mind you can now copy the above presented function and create it in your Google Sheets document as follows.
Go to the menu bar and click in: Data>Named functions. A small window in the right should appear. Fill the empty fields as shown,
 |
| Fig. 03 User defined function creation steps. |
In order to avoid mistakes just paste the function from this post. Hit next and you are done, the function has been created. It is now ready to be used as any other built it function of Google Sheets.
3 How to use the created function
This step should be easy. Once you have your data table and the user difeind function created go to the sheet and place the cursor in an empty cell. Let us suppose you want the density of water at 68.5 F. You should then write something as,
=LNRINTP(68.5)
Next, hit enter and the calculation would produce: 62.295.
Take your time to built this function in your own sheet. I will save lots of time.
This is the end. I hope, you find this post very useful.
Other stuff of interest
- LE01 - AC and DC voltage measurement and continuity test
- LE 02 - Start and stop push button installation 24V DC
- LE 03 - Turn on/off an 24V DC pilot light with a push button
- LE 04 - Latch contact with encapsulated relay for turning on/off an AC bulb light
- LE 05 - Emergency stop button installation
- About PID controllers
- Ways to control a process
- About pilot lights
- Solving the Colebrook equation
==========
Ildebrando.
No comments:
Post a Comment