Combining IF and SIGN functions in Excel

Question

So I'm trying to do something in Excel, in our lecture notes the teacher uses it, without explaining how.

For reference, this is the bisection method to find roots of an equation.

So I have 4 columns of data, all of which are calculated from each other.

In columns A and B I have the intervals on the data, which initially start between [2,3], but then change, depending on the sign of f(m). Initially B takes the value of each new midpoint, until the sign of f(m) changes, then B stays the same and A takes the value of the midpoint. You can see the first change happening in row 4.

M is simply the midpoint, so formula being (A+B)/2

f(m) is the function of the midpoint. So for example if the function we are trying to find the root of was x^2, f(m) would be 2.5^2

How can I combine the IF and SIGN functions to make this change automated?

If you need any further clarification please let me know.

Thanks

More generally - How can I combine IF and SIGN to make a cell reference change, based on the sign of another cell?

Solution

Try this formula for A starting in the third iteration (row 4):

=IF(SIGN($D3)=SIGN($D2),IF(A3=A2,A3,$B3),IF(A3=A2,$B3,A3))

You can apply the same to C as well.

=IF(SIGN($D3)=SIGN($D2),IF(C3=C2,C3,$B3),IF(C3=C2,$B3,C3))

This uses two levels of conditions; first it checks for sign change in f(m), and then checks whether the midpoint was previously being mapped to the lower or upper bound.

Sample output:

Comments

[Excel]

Would this be better suited in another stack?

[Alex M]

homework problem with no effort shown to resolve by OP. -1

[Excel]

Thanks, very useful! I'm assuming that this the earliest iteration that the formula can be started from?

[Excellll]

Well, before that, there's no chance for a sign change. Sure, you could amend the formula to handle the second row as well, but I wanted to stay away from that because it's not as simple as always defaulting to looking at the bottom half of the range in step 2. The first decision about which bound to replace with M depends on the sign of f(m) AND the sign of the derivative ("slope") of f(m).