Math question

Since you are converting an Excel spreadsheet you don't need to care about the mathematical meaning of this, you have to find out what exactly Excel does in this case. Iterating till it doesn't change anymore seems likely to me.

OpenOffice Calc by default gives an error message.

Well one would logically assume that, if two variables are inter-related, a loop will continue endlessly.

Well, those values are incorrect. Try substituting.

A = 4
B = A + (C*2)
C = B / 2

B = 4 + (20*2) = 40
C = 40/2 = 20

B = 44 = 40 // Something is wrong
C = 20 = 20

I believe what you have typed is impossible

A=4
B=A+(C*2)
C=B/2

B=4+(B/2*2)
B=4+B // B=4+B something is wrong

I stuck it in mathomatic, and it solved it to be 4=0. That's probably not good.

So you guys don't think i'm crazy:

My results are:

A5 4
A6 44
A7 20

so I don't know how you got 40 in A6.

[edit]

it results in error for me to put A6/2 into the A7 cell

Quote:

Original post by Dreq So you guys don't think i'm crazy:

Of course Excel outputs a wrong value if you gives it something impossible, try this:

A1 = A2+1
A2 = A1+1

This will also generate some weird result.

I am not able to enter the third cell in Excel 2003, it says it would be a circular function.

The weird thing is that i get the same error in a new workbook, but not in this one..

You are trying to solve what is called a simultaneous linear equation. Simplistic cell-based spreadsheets cannot do this, because they perform evaluation in a defined order by direct substitution. Because simultaneous equations refer to each other, such spreadsheets (correctly) tell you that there is a circular reference and (incorrectly) tell you that the equation is unsolveable. You'll want to visit this excellent page if you want to use Excel for solving SLEs. Excel also includes an equation solver that you can use as well.

However, in your particular case, Excel is correct in telling you the equation is unsolveable, as there is no real-valued solution to the system of equations you've provided:

1.) a = 4
2.) b = a + 2c
3.) c = b/2

Substitute (1) and (3) into (2) and you get the contradiction that "b = b + 4", clearly impossible. For instance, let b = 1; clearly 1 != 1 + 4.

hope that helps,

I have emailed the guy with the question, its his turn :D.. We asked for insurance formulas, they give us spreadsheets with errornous functions.. Another day in paradice for the lowly programmer eh ;) This scary thing is, the output of their excel formulas (cross reference and all) is correct O_o

Quote:

Original post by kSquared You are trying to solve what is called a simultaneous linear equation. Simplistic cell-based spreadsheets cannot do this, because they perform evaluation in a defined order by direct substitution.

As I said, the scary part is, it doesn't tell me theres a circular equation on this book, if i start a new excel file, I can't put the same function in.. they may have done something to disable this warning. And the even creepier part is the fact that it 'correctly' calculates the insurance premiums... *Bang head on desk*