|
Excel Iteration Program
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 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 |
Excel Iteration Problem/not capable??????????
why no expert is replying to my problem, is this a excel bug that it does not
do the iteration properly and need activation????????????? regards "raj74" wrote: 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 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 |
Excel Iteration Problem/not capable??????????
Function Interpolate(c1 As Range, c2 As Range, Target As Double) As Variant
The solution is C1:C3 = 4.00, 3.27, 3.84 Hi. I believe this function in C1:C3 can be eliminated. In C1, just put the equation =B1/2 and copy down to C3. As a side note, I believe you can recursively solve your equation when taking the limit at infinity. I've written it here as a vba function to make it easy to show. Here, k is your "+4" value, and f is your factor (1, .9, or .98) Function Fx(k, f) Fx = (f * k) / (2 - f) End Function Sub TestIt() Debug.Print Fx(4, 1) Debug.Print Fx(4, 0.9) Debug.Print Fx(4, 0.98) End Sub This returns the 3 solutions given in your example (and at a higher precision I might add) 4 3.27272727272727 3.84313725490196 -- HTH :) Dana DeLouis "raj74" wrote in message ... why no expert is replying to my problem, is this a excel bug that it does not do the iteration properly and need activation????????????? regards "raj74" wrote: 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 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 |
Excel Iteration Problem/not capable??????????
Thanks for the reply.
Actually what I am looking for why excel doesn't recalculate when specfic UDF (User defined Function) is used. I have given a sample to my problem.In actual problem my A col data is in circular ref with D col data, again B col is linked with A Col & C col inked with B col by respective formula. The output will be in C col based on the B col value. Interpolation fn (UDF) is used because to get the value of output C for corresponding value of B from a given (B,C) data range. Each time I have to activae the output cell py presing [F2] and [Enter] like similar situation given to you for the results.why it is not shown up automatically. Still I couldn't found out why, Is their any code to activate all the UDF or recalculate all the formula in a particular worksheet. I am using Excel 2000. If I could send you the softcopy... Any way thanks for your reply. Sincerely Raj New Delhi "Dana DeLouis" wrote: Function Interpolate(c1 As Range, c2 As Range, Target As Double) As Variant The solution is C1:C3 = 4.00, 3.27, 3.84 Hi. I believe this function in C1:C3 can be eliminated. In C1, just put the equation =B1/2 and copy down to C3. As a side note, I believe you can recursively solve your equation when taking the limit at infinity. I've written it here as a vba function to make it easy to show. Here, k is your "+4" value, and f is your factor (1, .9, or .98) Function Fx(k, f) Fx = (f * k) / (2 - f) End Function Sub TestIt() Debug.Print Fx(4, 1) Debug.Print Fx(4, 0.9) Debug.Print Fx(4, 0.98) End Sub This returns the 3 solutions given in your example (and at a higher precision I might add) 4 3.27272727272727 3.84313725490196 -- HTH :) Dana DeLouis "raj74" wrote in message ... why no expert is replying to my problem, is this a excel bug that it does not do the iteration properly and need activation????????????? regards "raj74" wrote: 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 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 |
Excel Iteration Problem/not capable??????????
Kindly Ignore the previous post,
The corrected post is here under (some col name was misplaced in the previous) __________________________________________________ _______________________________ Actually what I am looking for why excel doesn't recalculate a iteration when specfic UDF (User defined Function) is used. I have given a small example to my actual problem. In actual problem my A col data is in circular ref with D col data, again B col is linked with A Col & C col inked with B col by respective formula. The output will be in D col based on the C col value. Interpolation fn (UDF) is used to get the value of output D for corresponding value of C from a given (C,D) data range. Each time I have to activae the output cell py presing [F2] and [Enter] to get the correct results like similar situation I had posted previously. why the output value not shown up automatically. Still I couldn't found out why, Is their any code to activate all the UDF or recalculate all the formula in a particular worksheet. I am using Excel 2000. If I could send you the softcopy... Any way thanks for your reply. Sincerely Raj New Delhi "Dana DeLouis" wrote: Function Interpolate(c1 As Range, c2 As Range, Target As Double) As Variant The solution is C1:C3 = 4.00, 3.27, 3.84 Hi. I believe this function in C1:C3 can be eliminated. In C1, just put the equation =B1/2 and copy down to C3. As a side note, I believe you can recursively solve your equation when taking the limit at infinity. I've written it here as a vba function to make it easy to show. Here, k is your "+4" value, and f is your factor (1, .9, or .98) Function Fx(k, f) Fx = (f * k) / (2 - f) End Function Sub TestIt() Debug.Print Fx(4, 1) Debug.Print Fx(4, 0.9) Debug.Print Fx(4, 0.98) End Sub This returns the 3 solutions given in your example (and at a higher precision I might add) 4 3.27272727272727 3.84313725490196 -- HTH :) Dana DeLouis "raj74" wrote in message ... why no expert is replying to my problem, is this a excel bug that it does not do the iteration properly and need activation????????????? regards "raj74" wrote: 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 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 |
Excel Iteration Problem/not capable??????????
I'm not sure, but under Tools | Options, do you have "enable iterative calculation" turned on?
I'm using Excel 2007, so I'm trying to remember the wording. It should be similar. I thought for sure you would like the formula instead of looping? = (0.9*4) / (2 - 0.9) -- Dana DeLouis "raj74" wrote in message ... Kindly Ignore the previous post, The corrected post is here under (some col name was misplaced in the previous) __________________________________________________ _______________________________ Actually what I am looking for why excel doesn't recalculate a iteration when specfic UDF (User defined Function) is used. I have given a small example to my actual problem. In actual problem my A col data is in circular ref with D col data, again B col is linked with A Col & C col inked with B col by respective formula. The output will be in D col based on the C col value. Interpolation fn (UDF) is used to get the value of output D for corresponding value of C from a given (C,D) data range. Each time I have to activae the output cell py presing [F2] and [Enter] to get the correct results like similar situation I had posted previously. why the output value not shown up automatically. Still I couldn't found out why, Is their any code to activate all the UDF or recalculate all the formula in a particular worksheet. I am using Excel 2000. If I could send you the softcopy... Any way thanks for your reply. Sincerely Raj New Delhi "Dana DeLouis" wrote: Function Interpolate(c1 As Range, c2 As Range, Target As Double) As Variant The solution is C1:C3 = 4.00, 3.27, 3.84 Hi. I believe this function in C1:C3 can be eliminated. In C1, just put the equation =B1/2 and copy down to C3. As a side note, I believe you can recursively solve your equation when taking the limit at infinity. I've written it here as a vba function to make it easy to show. Here, k is your "+4" value, and f is your factor (1, .9, or .98) Function Fx(k, f) Fx = (f * k) / (2 - f) End Function Sub TestIt() Debug.Print Fx(4, 1) Debug.Print Fx(4, 0.9) Debug.Print Fx(4, 0.98) End Sub This returns the 3 solutions given in your example (and at a higher precision I might add) 4 3.27272727272727 3.84313725490196 -- HTH :) Dana DeLouis "raj74" wrote in message ... why no expert is replying to my problem, is this a excel bug that it does not do the iteration properly and need activation????????????? regards "raj74" wrote: 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 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 |
Excel Iteration Problem/not capable??????????
Everything I have checked in for iteration process like you mentioned in tool
option calculation. I have interested not the result of the file I have posted (in my first post) but the activation it requires (output cell having an UDF) to get the correct result. I was reading something called application.volatile in excel to force to start the calculation of UDF (user define function) to recalculate a particular sheet. But i don't know how to write the code or whether it is useful for my case. As an alternative soln, if any code can be written so that after running it, it will activate a formula (my interpolate UDf function) of a particular col (say, D1:D40)l and enter it to get all the output result what I am doing mannually. Thanks anyway for giving your time. sincerely Raj "Dana DeLouis" wrote: I'm not sure, but under Tools | Options, do you have "enable iterative calculation" turned on? |
Excel Iteration Problem/not capable??????????
Hellio!!!!!!!!!!
Thanks anyway, but I have dealing with 15 worksheet in my real problem and each time re enter all values is not possible. but I have got the answer from a friend of mine. Paste the below module and run the macro, which will give give you the result. Sub WorkaroundToForceUDFCalculation() Dim sht As Worksheet For Each sht In ActiveWorkbook.Worksheets With sht .Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End With Next sht End Sub Sincerely Raj New Delhi "Dana DeLouis" wrote: Hi. What I believe is happening is the following. Both A1, & B1 are not numbers, but are the results of a Custom Function in C1. When the Workbook is open, A1 & B1 do not have any information. This lack of Information is passed to your vba Function, and this returns a #Value error. This value error is passed back to A1 & B1, and the error goes back into C1. Hence you are stuck, and can't get out of this error loop. I don't believe Excel has any logic to get itself out of these types of loops when opened. You will have to re-enter the data in order for Excel to pick up the calculation again. The workaround for your situation might be the following. In the vba editor (Alt+F11) enter the following code on the "ThisWorkbook" module. When this particular workbook is opened, we make sure Iteration is turned on, and we re-enter your Formulas. This worked well for me using Excel 2007. Private Sub Workbook_Open() With Application '// Make Sure Iteration is turned on .Iteration = True .MaxIterations = 1000 .MaxChange = 0.0000001 'Re-Enter Formulas [C1:C3].Formula = [C1:C3].Formula End With End Sub -- "To understand recursion, one must first understand recursion." Dana DeLouis "raj74" wrote in message ... Everything I have checked in for iteration process like you mentioned in tool option calculation. I have interested not the result of the file I have posted (in my first post) but the activation it requires (output cell having an UDF) to get the correct result. I was reading something called application.volatile in excel to force to start the calculation of UDF (user define function) to recalculate a particular sheet. But i don't know how to write the code or whether it is useful for my case. As an alternative soln, if any code can be written so that after running it, it will activate a formula (my interpolate UDf function) of a particular col (say, D1:D40)l and enter it to get all the output result what I am doing mannually. Thanks anyway for giving your time. sincerely Raj "Dana DeLouis" wrote: I'm not sure, but under Tools | Options, do you have "enable iterative calculation" turned on? |
| All times are GMT +1. The time now is 02:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com