In performing engineering calculations, there are often times when you have to interpolate between values to get an answer to use in an equation. We know that similar triangles can be used to find that intermediate value, but how can we automate the process?
In Excel, the Trend function works well when you are trying to identify a number along a straight line. As long as you have your known X and Y values, and the new X value, you can easily determine the new Y value.
The format for the Trend Function is: =TREND(Known_y’s,Known_x’s,New_x)
As an example, when determining the seismic base shear, the Fa value must be calculated. This value is found through Table 1613.3.3(1) in IBC 2012, and is based on the site category, along with the Short Period Acceleration. If your Acceleration does not match one of the table values, then you need to interpolate between the adjacent values in order to determine Fa. The Trend function can be used to do this directly by using the Ss values bounding the actual value, and the corresponding bounding Fa values based on the Site Class.
The formula as written in cell C5 is: =TREND(G5:H5,G4:H4,C3)