Microsoft SharePoint 2016 List Formula Error

Paul 316 96 Reputation points
2021-02-23T03:30:04.533+00:00

Hello I am trying to use the IF(AND Formula in SharePoint List but I keep getting an error. The formula is below. =IF([Overall Rating Percentage]="<89%" , "Low Risk" , IF(AND([Overall Rating Percentage]="<79%",">=89%" , "Moderate Risk" , IF([Overall Rating Percentage]=">=79%" , "High Risk" ) ) )

SharePoint Server Management
SharePoint Server Management
SharePoint Server: A family of Microsoft on-premises document management and storage systems.Management: The act or process of organizing, handling, directing or controlling something.
2,861 questions
{count} votes

Accepted answer
  1. Paul 316 96 Reputation points
    2021-02-24T19:28:29.413+00:00

    I finally solved the issue just removed the AND part out of the formula and this is the formula I came up with and it works. I put negative 100 for the numbers that go below 0.

    =IF([Overall Rating Percentage]>89%
    ,"Low Risk"
    ,IF([Overall Rating Percentage]>79%
    ,"Moderate Risk"
    ,IF([Overall Rating Percentage]>-100%
    ,"High Risk")))

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Li Zhang_MSFT 1,566 Reputation points
    2021-02-23T09:38:55.723+00:00

    Hi @Paul 316 ,

    What is the type of Overall Rating Percentage column? Do you want to compare the value of Overall Rating Percentage column and then mark the corresponding risk level? Please provide more information about your requirement.

    This issue is caused by AND function you used, there is no error when I modify the formula like following, you could edit the formula based on it:

    =IF([Overall Rating Percentage]="<89%","Low Risk",IF(AND([Overall Rating Percentage]="<79%",[Overall Rating Percentage]=">=89%"),"Moderate Risk",IF([Overall Rating Percentage]=">=79%","High Risk")))   
    

    Result:

    71071-risk-level-result.png

    For more information, please refer to:

    IF function

    AND function

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.