Please calm down, my readers. I do able to communicate in English. For today topic, I would like to share an alternative way to perform interpolation on excel, which is not applicable in old version of Microsoft Office (older than 2007). However, there is no one single formula to complete this task. We need some working tables to help us.
For example, i want to know the properties of air in different temperature, T.
So, we have a blue color table as our raw data, green color table as our working table, and orange as display. The yellow cell is the data input. Prior to put any formula, i insert a "Trial No" column for both working table and display. This is the most pivotal part in this trick.
Now, we have the formula TREND in working table. Please notice that I only choose 2 rows for "known Y" and "known X" under TREND function. Herein, I use combination of IF and ISERROR function to return the cell to blank instead of #value. Ya, I loathe to have this. Blank cells give me a clearer image. Next, lock the cell accordingly before you drag the formula.
Well, we move to the trial no at the display table. We want to emphasis we able to know the trial no from the working table when we have the input in yellow column. So, we can use VLOOKUP function. However, whatever input we give not necessary match with the figure in the first column of data table exactly. So, be watchful, the forth/last criteria of VLOOKUP function should set as TRUE. Again, IF and ISERROR is nothing here, just to keep the worksheet clean.
All right. We want the input T in working table show the input temperature of yellow column at the right row. So, a simple IF function will do. IF(Trial No at working table=Trial No at display table,show input temperature, ""/Blank). Drag the formula to the end. So far so good?
Last, we use simple VLOOKUP function, to take the result from the working table to display table, using trial no as the guide. Lock the appropriate cells before dragging, and change the column no accordingly as well.
That's all. Done. Now, let us try, for 0 degree Celsius=273.15K, what is corresponding the gas properties?
I could figure out some common mistakes which cause the failure of interpolation using this technique. So, do a checking if your desired result doesn't appear.
1. For VLOOKUP function, always select the table where the first column is the reference item. For example, the Trial No must insert at the first column of working table because it will be referred by the display table.
2. For TREND function, choose only 2 rows for both "known Y" and "known X". If you choose more than 2, the result will lose its accuracy. You may know better if you know the working theory of TREND.
Well, I hope this will help everyone. Good day.