Microsoft Office Tutorials and References
In Depth Information
USE TWO-WAY INTERPOLATION WITH A SINGLE FORMULA
The formula fi 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.
Part
I
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
of times.
However, it is an interesting technique, and there have been a few instances at
the MrExcel message board where Hady’s approach was suggested:
http://www.mrexcel.com/forum/showthread.php?t=309945
http://www.mrexcel.com/forum/showthread.php?t=325172
http://www.mrexcel.com/forum/showthread.php?t=150637
Summary: You can use a self-referencing formula to replace a VBA user-
defi ned function.
Source: http://www.mrexcel.com/forum/showthread.php?p=1107631
The post was nominated by Andrew Fergus.
USE TWO-WAY INTERPOLATION WITH A
SINGLE FORMULA
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
parameter.
A simple example is a two-column table comprising
height above ground (the control parameter) and wind
Height
Velocity
20
10
30
40
40
130
50
180
60
240
 
 
Search JabSto ::




Custom Search