We are converting excel sheet to web base so we need to apply the same excel(circular reference) logic in web base application development.
When we have to calculate a value for the F3 cell it uses the G3 cell's value and the G3 cell's formula also uses the F3 cell value. we need to implement this logic in PHP.
In excel we have the following formula:
F3 = 0/590.07+(1.45+0.055*G3/1000)/8.42-0
(Result is = 0.21096279)
G3 = (38.2-F3*5.87)/(6.23+E3)*1000
(Result is = 5932.848864)
Maximum Iterations: 100
Maximum Change: 0.001
Any help to calculate that same result in PHP?
In Excel
circular references only are possible to calculate if iterative calculation is enabled. And defaults are: Use up to 100 iterations and stop iteration if the values change is lower than 0.001. So you need to do the same.
VBA
code which can be taken as pseudo code:
Sub CIRCULAR()
E3 = 0
F3 = 0
G3 = 0
maxDelta = 0.001
For i = 0 To 100
F3a = F3
G3a = G3
F3 = 0 / 590.07 + (1.45 + 0.055 * G3 / 1000) / 8.42 - 0
G3 = (38.2 - F3 * 5.87) / (6.23 + E3) * 1000
If Abs(F3a - F3) < maxDelta And Abs(G3a - G3) < maxDelta Then Exit For
Next i
resultF3 = F3
resultG3 = G3
End Sub
Btw.: Your results only are correct if E3
contains 0
. And it is not clear why the terms 0/590.07
and -0
are in formula in F3
as they are useless at all.