R
raj74
I have an annoying situation when I each time open the Ms excel file I
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
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?? Kindly help.
Regards
Raj
PS : Approximate 10-15 iteration it takes before converging.
Could I attach the .xls file.
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?? Kindly help.
Regards
Raj
PS : Approximate 10-15 iteration it takes before converging.
Could I attach the .xls file.