R

#### raj74

have to activate(pressing F2 and entering) the output cell (Col C) which

is having a circular reference with Col A to get the desired value but it is

lost when i reopen it next time and show #value.

The ouput cell C is having a interpolate function (a macro, attached below)

to interpolate from a range of value given separately for the value of

corresponding B

cell which is indirectly having a circular reference with Col A.

To make it clear lets see the File:

The three data range

A1 B1 C1

A2 B2 C2

A3 B3 C3

Now the three cols are linked by following relation

A = 4 + C

B = constvalue x A, Here B1 = 1.0 * A1, B2 = 0.9 * A2, B3 = 0.98 * A3

C = Function of (B)

C value is retrived by interpolation between a range of B & C, Given

separately, For that a macro has been writeen.

The data range from which C will interpolate the output value for

corresponding B value are as follows:

B C

0.00 0.00

2.00 1.00

4.00 2.00

6.00 3.00

8.00 4.00

10.00 5.00

The solution is C1: C2:C3 = 4.00:3.27:3.84

The macro for interpolation function is as below

---------------------------------------------------

Option Explicit

Function Interpolate(c1 As Range, c2 As Range, Target As Double) As Variant

' Interpolation Function

Dim i As Integer

Dim x1 As Double

Dim x2 As Double

Dim y1 As Double

Dim y2 As Double

Dim numRows As Integer

numRows = c1.Rows.Count

For i = 1 To numRows

If c1.Cells(i, 1).Value > Target Then

Exit For

End If

Next i

x1 = c1.Cells(i - 1, 1).Value

x2 = c1.Cells(i, 1).Value

y1 = c2.Cells(i - 1, 1).Value

y2 = c2.Cells(i, 1).Value

Interpolate = (y2 - y1) * (Target - x1) / (x2 - x1) + y1

End Function

---------------------------------------------

Could this problem be solved so that iteraion automatically starts without

any activation?? Kindly help.

Regards

Raj