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.

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-

deﬁ 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