Microsoft Office Tutorials and References
In Depth Information
USE TWO-WAY INTERPOLATION WITH A SINGLE FORMULA
The formula ﬁ nally gets to use the last part of the formula, where it takes the
RIGHT characters from B1, this time grabbing only the LEN of A1. This strips
out the leading 0 , as shown in Figure 62.
Figure 62. h e i nal portion of the formula strips of the leading 0
when the LEN of B1 is greater than the LEN of A1.
Additional Details: Although this formula can do something VBA-like without
using any VBA, it has limited use. You cannot copy the formula down to any
other cells. And if you change A1, you need to start over and press F9 a bunch
However, it is an interesting technique, and there have been a few instances at
the MrExcel message board where Hady’s approach was suggested:
Summary: You can use a self-referencing formula to replace a VBA user-
deﬁ ned function.
The post was nominated by Andrew Fergus.
USE TWO-WAY INTERPOLATION WITH A
Challenge: Many engineering design problems require
designers to use tables to compute values of design
parameters. Such tables contain values of the required
parameter for a range of values of a control parameter,
arranged in discrete intervals, and the designer is
permitted to use linear interpolation for obtaining the
parameter value for intermediate values of the control
A simple example is a two-column table comprising
height above ground (the control parameter) and wind