Hi everyone,
I need to do an iterative calculation that is giving me some trouble.
lets say my initial value is 10 and is in cell A1 (it comes from a monitoring device)
From this value I calculate my first reference value B1=.9*A1
with B1 I can calculate C1 which is just a function of B1 (C1=f(B1)
with both B1 and C1, a new value of B1 is calculated and it is in D1 (D1=f(B1,C1)
Now using D1 I have to recalculate C1 (C1=f(D1)), and using both C1 and D1 I will obtain a new value of D1 (lets called D1')
This cycle has to be repeated until the difference between D1 and D1' is 0.001
I know it sounds like a circular reference. I enabled the iterations in my workbook and at the beginning it works, but every time I change, add, erase anything in the spreadsheet the "final" value of D1' keeps changing. And keeps changing until all my "final" values of D1' are the same
At the beginning, everything is good as I am monitoring some properties on the field and my field readings change in time, but it does not make sense all my readings will end being the same as my starting reference values are different.
I am not sure what I am doing wrong. It seems very easy from the perspective of a circular reference, but I also know working with the is a double edge sword.
Hope you can help me
I need to do an iterative calculation that is giving me some trouble.
lets say my initial value is 10 and is in cell A1 (it comes from a monitoring device)
From this value I calculate my first reference value B1=.9*A1
with B1 I can calculate C1 which is just a function of B1 (C1=f(B1)
with both B1 and C1, a new value of B1 is calculated and it is in D1 (D1=f(B1,C1)
Now using D1 I have to recalculate C1 (C1=f(D1)), and using both C1 and D1 I will obtain a new value of D1 (lets called D1')
This cycle has to be repeated until the difference between D1 and D1' is 0.001
I know it sounds like a circular reference. I enabled the iterations in my workbook and at the beginning it works, but every time I change, add, erase anything in the spreadsheet the "final" value of D1' keeps changing. And keeps changing until all my "final" values of D1' are the same
At the beginning, everything is good as I am monitoring some properties on the field and my field readings change in time, but it does not make sense all my readings will end being the same as my starting reference values are different.
I am not sure what I am doing wrong. It seems very easy from the perspective of a circular reference, but I also know working with the is a double edge sword.
Hope you can help me