Forum Discussion

MikeBrost's avatar
MikeBrost
Copper Contributor
Apr 01, 2023

If Statements For Calculated Column Beyond 19 Nested Cap - HELP

I've got one column I'm using as source for my nested if statements with more than 40 different values that I need to transform to different values to be ingested.  I had to create three columns to break the nested if statements across and a final column that I want to pull the information from the three columns into.  Here's start of my formula:

 

=IF([Nonclinical/Clinical Study Subtype calculated]<>"No",[Nonclinical/Clinical Study Subtype calculated],
IF([Nonclinical/Clinical Study Subtype calculated2]<>"No",[Nonclinical/Clinical Study Subtype calculated2]))

 

If value is = "No" in [Nonclinical/Clinical Study Subtype calculated] column, I want to display the data from [Nonclinical/Clinical Study Subtype calculated2]

If value is <> "No" in [Nonclinical/Clinical Study Subtype calculated], I want to display the data from [Nonclinical/Clinical Study Subtype calculated] column.  

 

The formula populates fine for [Nonclinical/Clinical Study Subtype calculated], but it just returns false for [Nonclinical/Clinical Study Subtype calculated2] even when data is present that should work.  Can anyone please assist on my issue?  I'm floundering...

 

Lists
microsoft lists
SharePoint Online

4 Replies

  • ganeshsanap's avatar
    ganeshsanap
    MVP
    Apr 01, 2023

    MikeBrost Try using below formulas: 

     

    =IF([Nonclinical/Clinical Study Subtype calculated]="No",[Nonclinical/Clinical Study Subtype calculated2],[Nonclinical/Clinical Study Subtype calculated])

     

    OR 

     

    =IF([Nonclinical/Clinical Study Subtype calculated]="No",IF([Nonclinical/Clinical Study Subtype calculated2]="No","",[Nonclinical/Clinical Study Subtype calculated2]),[Nonclinical/Clinical Study Subtype calculated])

     

    Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

    For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

  • Varun8492's avatar
    Varun8492
    Copper Contributor
    Apr 01, 2023
    Based on the information provided, it seems that the formula is not checking if the value in the [Nonclinical/Clinical Study Subtype calculated2] column is equal to "No". In order to do this, you can add another IF statement within the second argument of the first IF statement, like this:

    =IF([Nonclinical/Clinical Study Subtype calculated]<>"No", [Nonclinical/Clinical Study Subtype calculated],
    IF([Nonclinical/Clinical Study Subtype calculated2]="No", "", [Nonclinical/Clinical Study Subtype calculated2]))

    This formula checks if the value in [Nonclinical/Clinical Study Subtype calculated2] is equal to "No". If it is, it returns an empty string (""), indicating that there is no data to display. If it is not equal to "No", it returns the value in [Nonclinical/Clinical Study Subtype calculated2].

    Make sure that the column names in the formula match the actual column names in your sheet and that the column containing the formula is formatted to display the correct data type.
    • MikeBrost's avatar
      MikeBrost
      Copper Contributor
      Apr 01, 2023
      So, I updated my formula to include <>FALSE rather than "No" and the formula seems to be working. The odd thing is the values are not being displayed in the SharePoint site for my final column, but when I export to Excel the values are present. Odd?
      • Varun8492's avatar
        Varun8492
        Copper Contributor
        Apr 01, 2023

         

        If the values are present when you export the data to Excel, but not in the SharePoint site, there could be a few potential issues that may be causing this problem.

        One possibility is that the column formatting is not set correctly in SharePoint, which is causing the values to be displayed incorrectly or not at all. You may want to check the column settings and ensure that the column type is set correctly and that the formatting is set to display the data in the correct way.

        Another possibility is that there may be some permissions or access issues that are preventing the data from being displayed in the SharePoint site. You may want to check the permissions and access settings for the site and ensure that the appropriate users have access to view the data.

        Lastly, there may be some caching or refresh issues with the SharePoint site that are causing the data to not be displayed correctly. You may want to try clearing the cache or refreshing the site to see if that resolves the issue.

        MikeBrost 

         

        Please mark as solved if this solves your problem

Resources