Using IF functions in word

Question

Hello,

I am a complete beginner trying to make an IF statement in microsoft word. I want to be able to input a number into a table and a specific condition to appear based on the number that was input as shown in the below table. (Input on the left and the output on the right.)

≤ 0.3	2 - 10 %
0.3 - 0.4	5 - 10 %
0.40 - 0.45	5 - 25 %
0.45 - 0.60	10 - 30 %
0.60 - 0.70	20 - 40 %
0.70 - 0.80	25 - 50 %
0.80 - 1.00	30 - 60 %
1.00 - 1.20	40 - 80 %
1.20 - 1.40	50 - 95 %
1.40 - 1.45	60 - 100 %
≥ 1.5	70 - 100 %

As a test, I tried IF "Expression 1" = "Expression 2" "True "False" however my values kept appearing as false and I am not sure why?

Any help that could be provided would be greatly appreciated!

Thanks,

John

Answer 1

I gather that the expressions in the left column are the conditions that correspond to the outputs in the right column, and you want to step through the conditions until you find the one that matches the single input value.

To handle that, first insert a legacy form field in the template or document you're working on. I would set it to Number type, format 0.00, name the bookmark Input, and check the boxes "Fill-in enabled" and "Calculate on exit". (The bookmark name is your choice, but make it short because you're going to have to repeat it a lot of times in the output field.)

In the output cell, you need a set of nested IF fields, with each condition nested into the FALSE part of the IF field before it. The first few nestings go like this:

{ IF { Input } <= 0.3 "2 - 10 %" { IF { Input } <= 0.4 "5 - 10 %" { IF { Input } <= 0.45 "5 - 25 %" } { IF { Input } <= 0.6 "10 - 30 %" <the next IF field goes here> } } }

The FALSE part of the most deeply nested IF field is just "70 - 100 %". Note that your list has a gap between 1.45 and ≥ 1.5 that should be expressed as IF { Input } ≤ 1.5 .

To make the form field operational, protect the document for filling in forms.

There is an alternative method that involves using content controls instead of fields, and writing a Document_ContentControlOnExit macro to take the place of the IF fields. This can be easier to write and to understand, and doesn't require locking the document. Post back if you want instructions for this.

Answer 2

If needed, here is my article on Nested IF Fields - A method to create them.

Answer 3

Hi Jay,

Thanks for the response! How do I add the {input}? Is it via ‘Insert’ ‘quickparts’ ‘field’ ‘mergefield’ type ‘input’ ? Then do I have to delete the contents of the field to get rid of the << each side of input?

As a test I tried the below

After updating fields the result was as follows

This should have resulted in "complete"

It seems to me that I am having trouble trying to get the IF field to recognise where to read the input data from?

Would be great to hear about your alternate way if it is easier to setup and read? Although I would like to understand why this way is not working for me :)

Thanks,

John

Answer 4

Hi John,

I'm sorry, I assumed too much background about form fields in my previous reply. I'll go step by step here. The explanation of the other method, using content controls, will be in another reply in a little while.

First, if you don't see a Developer tab on your ribbon, you need to enable it. It's hidden by default. Go to File > Options > Customize Ribbon. In the right-hand list, put a check in the box next to Developer and then click OK.

Next, put the cursor in the table where you want to insert the value (in your screen shot, it would be in the Retention time cell of the table). Click the Developer tab to display its ribbon, and click the Legacy Tools button, the one shown in this picture:

The dropdown that appears has two sections, Legacy Forms and ActiveX Controls. Click the first button in the Legacy Forms section, the one with ab in the rectangle. DO NOT use ActiveX controls, as they don't work with the IF field the same way.

That inserts a text form field in the document at the cursor location. By default, the text form field has a gray shade, although you can turn that off by clicking the fifth button in the Legacy Forms group.

Next, set up the properties of the field you just inserted, by clicking the Properties button while it's enabled. That opens the following dialog box:

In this screen shot, I show the values I suggested in my previous reply. By setting the Type box to "Number", you ensure that the form field won't accept characters that aren't part of the number format. The Number Format of "0.00" means that whatever number is entered will be automatically formatted to two decimal places. The "Calculate on exit" means that any IF field that refers to this form field will be updated automatically when the field's value changes.

Whatever you enter in the Bookmark box will be the "name" of the form field. I suggested "Input", but you could use any valid bookmark name, for instance "Retention". Whatever you put there, you must use that name in all of the nested IF fields where I showed {Input}. So if you do enter Retention in the Bookmark box here, then the IF field would look like {IF {Retention} <= 0.3 ... etc,

When the property values are complete, click the OK button. If you need other places in the document to enter data, you'll have to insert form fields in those places too. Each one must have a unique Bookmark "name" -- if you use the same name a second time, that field will "steal" the name from the other field, which will be left nameless (not good!). Once the document or template is protected (as discussed in the next paragraph), it won't be possible to type or format any text that isn't inside a form field.

Finally, when all the fields and other text are complete and correct, click the Restrict Editing button on the Developer ribbon (or its twin on the Review ribbon). In the pane that opens, check the box under Editing Restrictions, select "Filling in forms", and click the "Yes, Start Enforcing Protection" button. You'll be prompted to enter a password (twice!) if you want one, but you can leave it blank.

Until you start protection, none of the form fields will operate -- trying to enter text in one will just overwrite the field and delete it.

If you want to make changes in the document, open the pane and click the Stop Protection button. You can go back and forth as many times as necessary.

Answer 5

This reply is about using content controls instead of legacy form fields.

The first step is the same as in the previous reply: Make Word display the Developer tab on the ribbon.

With the cursor in the Retention Time cell, click the Plain Text Content Control button, the second from left in the top row of the Controls group.

That inserts a content control at the cursor position. Click the Properties button when it's enabled, to open this dialog:

Enter a title in the Title box. Unlike the case of the legacy form field, the title isn't required to be a bookmark name, but it's a good idea to make it describe the control's purpose and to keep it short. I recommend checking the box for "Content control cannot be deleted" because you want to make sure the control is available to run the macro. Click OK to save the settings.

Next, insert another plain text content control in the Gradient Used cell, and set its title to Gradient used (or something similar). Also check the "cannot be deleted" box, and click OK.

If you want to change the default placeholder text, click the Design Mode button. The display changes; the blue-outlined boxes at either end show the title, and the placeholder is shaded gray. You can edit the placeholder:

Click the Design Mode button again to return to the normal display.

I'm going to give you a bare-bones macro that just puts the corresponding text in the Gradient control when the cursor leaves the Retention Time control.

With the cursor in your document or template, press Alt+F11 (on some laptops, Alt+Fn+F11) to open the macro editor. You should see a Project pane on the left side; if it isn't there, click View > Project Explorer. This macro must be stored in the ThisDocument module of the document or template:

Double-click that icon to open the large (currently blank) code window on the right. Copy the following code and paste it into the code window, and then save (Ctrl+S).

Option Explicit 

Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean) 

    If CC.Title <> "Retention time" Then Exit Sub 

    Dim strGradient As String 

    Dim sngRetTime As Single 

    If CC.ShowingPlaceholderText Or CC.Range.Text = "" Or Not IsNumeric(CC.Range.Text) Then 

        strGradient = " " 

    Else 

        sngRetTime = Val(CC.Range.Text) 

        Select Case sngRetTime 

            Case Is <= 0.3 

                strGradient = "2 - 10 %" 

            Case 0.3 To 0.4 

                strGradient = "5 - 10 %" 

            Case 0.4 To 0.45 

                strGradient = "5 - 25 %" 

            Case 0.45 To 0.6 

                strGradient = "10 - 30 %" 

            Case 0.6 To 0.7 

                strGradient = "20 - 40 %" 

            Case 0.7 To 0.8 

                strGradient = "25 - 50 %" 

            Case 0.8 To 1# 

                strGradient = "30 - 60 %" 

            Case 1# To 1.2 

                strGradient = "40 - 80 %" 

            Case 1.2 To 1.4 

                strGradient = "50 - 95 %" 

            Case 1.4 To 1.45 

                strGradient = "60 - 100 %" 

            Case 1.45 To 1.5 

                strGradient = "65 - 100 %"   ' what should this be? 

            Case Else 

                strGradient = "70 - 100 %" 

        End Select 

    End If 

    ActiveDocument.SelectContentControlsByTitle("Gradient used")(1).Range.Text = strGradient 

End Sub 

You may need to make a few changes:

• If the title you entered for the retention time content control isn't exactly "Retention time" (including the same capitalization), change the name between the double quotes in the line that starts "If CC.Title" to match the control's title exactly.
• Similarly, if the title of the gradient content control isn't exactly "Gradient used", change the name between the double quotes in the next-to-last line to match the control's title.
• Under "Case 1.45 to 1.5", I've guessed what the gradient expression should be. Change it if necessary.

Here's what the macro does:

Whenever the cursor exits from any content control in the document, Word automatically starts the macro. The items between the parentheses in the Sub line are called "arguments", and Windows fills them with values at the start of the macro. The argument named CC points to the control that the cursor is exiting.

The first If statement says that if the Title of CC isn't "Retention time" (or whatever you changed this to), the macro should do nothing, just immediately stop running.

The two lines that start with "Dim" create storage space for two variables, one a String (a piece of text) and the other a Single (a number that may have decimal places).

The next If statement and the line after it say that if the CC control's placeholder text is showing or if the control is empty, or if it contains text that isn't a number, then the gradient expression will be a space character, nothing else. That way, there's no confusion about some gradient range being displayed when there's no valid retention time value.

If the text in CC passes the validity test, the code in the Else section executes. First it uses the Val function to convert the text string CC.Range.Text into a number, which it stores in the sngRetTime variable. The section that goes from the Select Case statement to the End Select statement figures out which range the sngRetTime value falls into. It tests each Case statement, starting from the top. If the condition isn't met, it goes on to the next Case. When it finds a condition that's True, it executes the statement under it, assigning a value to the strGradient variable, and then jumps to the End Select statement. If none of the preceding cases were True, the Case Else assignment is the final value.

Lastly, the line that starts with ActiveDocument.SelectControlsByTitle locates any controls in the document that have the title shown between quotes. Because more than one control can have the same title, the result is a collection of zero or more controls, and the (1) selects the first one in the collection. (Normally you'd have to test whether there's at least one control in the collection, to avoid a runtime error if there are zero in the collection. Because the gradient control in the document or template should have the "cannot be deleted" box checked, that one should always be present. If you see the error "The requested member of the collection does not exist", the problem is probably that the macro doesn't exactly match the control's title, such as a different capitalization.) The Range.Text of that control is set to whatever text is in the strGradient variable.