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.

Search JabSto ::

Custom Search