0

I need some help. I have created a pretty extensive list at work for my colleagues to use to track financials on some of our projects, I am using a formula one of the project managers had established in excel and I need it to work in the same capacity in SharePoint, but I am getting a syntax error. Column C pulls data from A [Assigned Risk Value] which is a drop down menu of 5 choices-

(remote, critical, high,moderate,low)

and column B [LOE #] is a constant number. All dropdowns are associated with a certain percentage that determine the number in Column C

(remote,0)(critical, 1)(high,.75)(moderate,.5)(low, .25)

So here is the formula I have created & it works

=IF([Assigned Risk Value]="low",.25)*[LOE #] 

When I add the other criteria from my dropdown I get error. Can someone please show me how I can add all five and not get an error?

  • You need to nest conditional if statements, could you please add the full statement (where you're getting a syntax error) so we can correct it for you? – Michael A Sep 25 '13 at 4:24
1

I can't test this, but this and you didn't include your errored statement so I can only give you the tools to solve your problem. For reference, here is the documentation on the IF Function in SharePoint: http://office.microsoft.com/en-au/windows-sharepoint-services-help/if-function-HA001161010.aspx

In case that link ever dies in the future, here's the pertinant information for retentions sake:

Syntax

IF(logical_test,value_if_true,value_if_false)

Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, [Quarter1]=100 is a logical expression; if the value in one row of the column, [Quarter1], is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.

Value_if_true is the value that is returned if logical_test is TRUE. For example, if this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, then the IF function displays the text "Within budget". If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula.

Value_if_false is the value that is returned if logical_test is FALSE. For example, if this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, then the IF function displays the text "Over budget". If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula.

Note this line, it will help you to understand the code example below:

Value_if_false can be another formula.

In our case we're testing if something is true, if it isn't then we need to perform another test. We keep doing these tests until no more options are available. In your case, your statement will look something like the following once complete:

=IF([Assigned Risk Value]="low",.25,
      IF([Assigned Risk Value]="moderate",.5,
            IF([Assigned Risk Value]="high",.75,
                  IF([Assigned Risk Value]="critical",1,
                        IF([Assigned Risk Value]="remote",0, "Unknown"))))) * [LOE #] 

Basically, we're doing a test, if the value isn't true, we run another test, and so on and so forth. If you try this and you still receive an error please post the details here and I'll check back for you!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.