Excel – Combining IF and SIGN functions in Excel

microsoft excelworksheet-function

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.

enter image description here

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?

Best Answer

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:

enter image description here

Related Solutions

Excel – Converting “in place” a column of cells from minutes to hours and minutes in Excel

I agree with Excellll; you're going to need VBA to convert the values in place to a string value.

This method won't result to text strings; but it might help you get the format you need.

Let's say these were my minutes:

enter image description here

Type 1440 into any blank cell. Select this cell and press Ctrl + C.
Select the range that contains the minutes.
Paste Special > Values > Divide
Click OK. Result:
Select the range of number again and press Ctrl + 1.
Go to Number > Custom
Type the format:

[h] "h" mm "m"

Result:

enter image description here

If you'd like to change them back to minutes:

Type 1440 into any blank cell. Select this cell and press Ctrl + C.
Select the range that contains your hours/times.
Paste Special > Values > Multiply.
Press Ctrl + 1. Set the number format back to General.
You may get rid of the 1440 cell when you're done.

To get the number of hours in decimal form, use 60 instead of 1440. So for 330 (mins), you'll get 5.5 (hours).

Excel VBA Function for Value AND Format Lookup

Sub test()
    On Error GoTo Suberror
    'turn off screen updating
    Application.ScreenUpdating = False
    'ask for input
    strName = InputBox(Prompt:="Lookup Value", _
              Title:="Lookup Value", Default:="0")
        'if no input - exit
        If strName = "0" Or _
               strName = vbNullString Then

               Exit Sub
        'otherwise Find
        Else

            Columns("A:A").Select
            Selection.Find(What:=strName, After:=ActiveCell, _
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, _
            SearchFormat:=False).Activate

            'Copy
            ActiveCell.Offset(0, 1).Copy

        End If

        'Paste to the range that you define
            Range("J1").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
Suberror:
    Range("A1").Select
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

    End Sub

Related Question