Hi! I have looked all over for an answer to this so I apologize if it has been answered elsewhere.

I am trying to create a nested if statement in a Word table based on a drop down box in one of the table's cells.



Cell B2: Drop box (High, Medium, Low)
Cell B3: Any numerical value (We can use 100 for the example)
Cell B4: If formula

I have (successfully!) created this formula in excel, but I can't seem to get it right in Word. Instead of posting my terrible attempts in Word I have included the Excel formula below. Could someone please help me with the proper Word formula???

=IF(B2="High",B3/(1+B3*0.2^2),IF(B2="Medium",B3/(1+B3*0.1^2),IF(B2="Low",B6/(1+B6*0.05^2),"FALSE")))

The main problem you'll have trying to do this in Word is that Word's table referencing formulae don't recognise text. Consequently, they can't test for 'High', 'Medium', or 'Low'. If your drop down box is a formfield dropdown in a document with forms protection applied, though (but not a content control dropdown), this is not an impediment. Assuming you are working with a formfield dropdown, the field code to do the calculation in Word is:
{IF{REF Dropdown1}= "High" {=B3/(1+B3*0.2^2)} {IF{REF Dropdown1}= "Medium" {=B3/(1+B3*0.1^2)} {IF{REF Dropdown1}= "Low" {=B3/(1+B3*0.05^2)} "FALSE"}}}
where Dropdown1 is the formfield dropdown's internal bookmark name and you set it's 'calculate on exit' property.

Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues. The spaces represented in the field construction are all required.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you for the response!

I still seem to be having some trouble (shocking!)...

1) I was using a content control dropdown, so I have switched that to the form field. The data in cell B3 can vary and I need to be able to change the input. Is there a way to allow that cell to be edited after protection is enforced?

2) After the formula, dropbox, and cell B3 are all correct. What is the best way to re-run the formula when one or more of the inputs get changed? Is this supposed to be automatic?

3) Finally, I am not sure if I have something incorrect with my formula. It spit out a number the first time, but when I tried to run it again I got a syntax error. I have attached a shot of the formula.


I would really appreciate any more assistance you could provide. Thank you so much for your help!

Attached Images

Formula.PNG (13.7 KB, 53 views)

Actually, the formula seems to be working well now.

So my only issues relate to #1 and #2 above.

Thanks

For #1, you can use a text formfield. As with the dropdown formfield, you should set it's 'calculate on exit' property.

For #2, setting the 'calculate on exit' property will take care of any updates.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]