Microsoft Office Tutorials and References

In Depth Information

**USE TWO-WAY INTERPOLATION WITH A SINGLE FORMULA**

Building the Mega-formula

You can now integrate the formulas in J3:J17 into a single formula to get the

required wind pressure:

Copy the text of the formula in J17 to, say, J20—so the formula in J20 is:

=J15+(J16-J15)*J12/J14

Substitute the cell references for each precedent cell in this formula with

the formula in the cell. To illustrate, the ﬁ rst cell reference is J15, which

occurs at two places in the formula:

=J15+(J16-J15)*J12/J14

The formula in cell J15 is:

=J7+(J8-J7)*J11/J13

Copy the text of the formula (without the = sign) and replace the J15s in

the formula in J20 so that the formula now becomes:

= J7+(J8-J7)*J11/J13 +(J16- J7+(J8-J7)*J11/J13)*J12/J14

Do the same with the remaining references J16, J12, and J14.

Successively repeat the procedure of back-substitution for the new set of

references until all references in the formula are reduced to the deﬁ ned

names ColHd, RoHd, Dat, Ht, and Sp.

The resulting formula, after all substitutions, is:

=((INDEX(Dat,(MATCH(Ht,RoHd)),(MATCH(Sp,ColHd))))+((INDEX(Dat,

((MATCH(Ht,RoHd))+1),(MATCH(Sp,ColHd))))-(INDEX(Dat,(MATCH(Ht,

RoHd)),(MATCH(Sp,ColHd)))))*(Ht-INDEX(RoHd,(MATCH(Ht,RoHd))))/

(INDEX(RoHd,((MATCH(Ht,RoHd))+1))-INDEX(RoHd,(MATCH(Ht,RoHd)))))+((

(INDEX(Dat,(MATCH(Ht,RoHd)),((MATCH(Sp,ColHd))+1)))+((INDEX(Dat,((M

ATCH(Ht,RoHd))+1),((MATCH(Sp,ColHd))+1)))-(INDEX(Dat,(MATCH(Ht,Ro

Hd)),((MATCH(Sp,ColHd))+1))))*(Ht-INDEX(RoHd,(MATCH(Ht,RoHd))))/

(INDEX(RoHd,((MATCH(Ht,RoHd))+1))-INDEX(RoHd,(MATCH(Ht,RoHd))))

)-((INDEX(Dat,(MATCH(Ht,RoHd)),(MATCH(Sp,ColHd))))+((INDEX(Dat,

((MATCH(Ht,RoHd))+1),(MATCH(Sp,ColHd))))-(INDEX(Dat,(MATCH(Ht,

RoHd)),(MATCH(Sp,ColHd)))))*(Ht-INDEX(RoHd,(MATCH(Ht,RoHd))))/

(INDEX(RoHd,((MATCH(Ht,RoHd))+1))-INDEX(RoHd,(MATCH(Ht,RoHd))))))*(Sp-

INDEX(ColHd,(MATCH(Sp,ColHd))))/(INDEX(ColHd,((MATCH(Sp,ColHd))+1))-

INDEX(ColHd,(MATCH(Sp,ColHd))))

This impressive-looking formula is 867 characters long and, of course, totally

incomprehensible in its ﬁ nal form.

Summary:
You can build a single formula from a multiple-step calculation,

using successive back-substitution, starting with the last formula.

1.

2.

3.

4.

5.