Linear interpolation and extrapolation in excel

[S4Tyler]

Hello,

I am working on a spread sheet to quickly provide engineers with seismic Fa and Fv values based on given soil class values (A through E), given Sa(0.2) (a number) and a given Sa(1.0) (a number) based on the Canada building code seismic Fa and Fv charts.

The charts are in the attached spreadsheet.

An example of what I need the spreadsheet to do:

given: Soil class = A
S(0.2) = 0.625
S(1.0) = 0.25

Fa is a linear interpolation where:
Fa=0.7 when Sa(0.2)=0.5 and Fa=0.7 when Sa(0.2)=0.75
So: when Sa(0.2)=0.625 then by interpolation Fa=0.75

Fv is a linear interpolation where:
Fv=0.5 where Sa(1.0)=0.2 and Fv=0.5 where Sa(1.0)=0.3
So: when Sa(0.2)=0.25 then by interpolation Fv=0.5

As you can see, the process for finding Fa and Fv is quite simple but this is something that is done several times every day and I thought a tool to automate this step would be a time saver.

Thanks in advance for any help anyone can offer.

Tyler

 

[Gruuuu]

I strongly recommend be done in Visual Basic. All of the logic can be done easily with Excel formulas except finding the bracketing values for a given value, unless I am mistaken. I know that any lookup function will find the next highest value, not sure about finding the next lowest.

Even still, it's probably easier in Visual Basic. If you want to try you luck there, you can post in

http://tek-tips.com/threadminder.cfm?pid=707

 

[SkipVought]

Hi,

Can do it quit easily in Excel on the sheet. Don't really need the chart, except for the visual.

Here's the drill for the Fa. Apply similary to the Fv
[tt]
1. the table of data

Site Class Values of Fa
Sa 0 0.25 0.5 0.75 1.0 1.25
ASa 0 0.7 0.7 0.8 0.8 0.8
BSa 0 0.8 0.8 0.9 1.0 1.0
CSa 0 1.0 1.0 1.0 1.0 1.0
DSa 0 1.3 1.2 1.1 1.1 1.0
ESa 0 2.1 1.4 1.1 0.9 0.9

2. Name the range with the names in Column A, using Insert > Name > Create --Create names in LEFT column.

3 The Data Entry area -- Name the CELLS using the same technique.

4. I added this calculation table starting in M1

M: N: O:
1: =MATCH(Sa_0.2,Sa,1) =M1+1
2: =INDEX(Sa,1,M$1) =INDEX(Sa,1,N$1) =(Sa_0.2-M2)/(N2-M2)
3: =INDEX(INDIRECT(Soil_Class),1,M$1) =INDEX(INDIRECT(Soil_Class),1,N$1) =(N3-M3)*O2+M3

5. The formula in Fa Output

=O3
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

1. You must ALSO name the A,B,C... ranges, in addition to the Sa range.

I would recommend using Tags in column A like...
[tt]
ASa
BSa
CSa
...
[/tt]
and then in the other table something different in order to differentiate between the ranges.

2. then you formulae will need to be mofdified...
[tt]
=INDEX(INDIRECT(Soil_Class&"Sa"),1,M$1)
[/tt]
3. Fix INDERECT in the other formula.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]