SOLVED

If/Then ISBLANK in SharePoint Online Calculated Column

Copper Contributor

Hello, 

I have a calculated column I've called "FailPass" that needs to look across a series of choice columns to calculate a score, but also account for blanks.

 

The below formula saves as a pass/fail score even if the user left some of the questions unanswered (question choice column is blank). I would rather the FailPass column show as blank until all the necessary questions are answered, which would allow a user to return to a saved item and finish completing it if they were interrupted during the task, or filter on items in the list that are without a FailPass score.

6 Replies

@CristinaMorales You can use formula in this format: 

 

=IF(OR(ISBLANK([Column 1]),ISBLANK([Column 2]),ISBLANK([Column 3]),ISBLANK([Column 4]),ISBLANK([Column 5])),"",<Replace this with your existing formula>)

 

<Replace this with your existing formula> replace this section with your existing formula which you are using for calculation.


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

Hello, I am sorry but this formula didn't solve my issue, and I haven't been able to get it to work. It's still showing pass/fail regardless of whether or not some questions are left unanswered/unscored even though it should be blank.

@CristinaMorales Can you show us the formula you are using currently? Note, you can use maximum 30 conditions inside OR function.


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

Hello, @ganeshsanap this is what I've been trying to get to work with no success. Only certain questions should result in a "fail" if they are inaccurate, otherwise they "pass". I do not want a result to show until all questions are answered.
=IF(OR([question1.?]="Inaccurate",[question 2?]="Inaccurate",[question 3?]="Inaccurate"),"Fail","Pass",IF(OR(ISBLANK([question 1?]),ISBLANK([question 2?]),ISBLANK([question3?]),ISBLANK([question 4?]),ISBLANK([question 5?]),ISBLANK([question 6?]),ISBLANK([question 7?]),ISBLANK([Question 8?]))," ","Pass")
best response confirmed by CristinaMorales (Copper Contributor)
Solution

@CristinaMorales Try formula like: 

 

=IF(OR(ISBLANK([question 1?]),ISBLANK([question 2?]),ISBLANK([question3?]),ISBLANK([question 4?]),ISBLANK([question 5?]),ISBLANK([question 6?]),ISBLANK([question 7?]),ISBLANK([Question 8?])),"",IF(OR([question1.?]="Inaccurate",[question 2?]="Inaccurate",[question 3?]="Inaccurate"),"Fail","Pass"))

 


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

This worked. Thank you so much.
1 best response

Accepted Solutions
best response confirmed by CristinaMorales (Copper Contributor)
Solution

@CristinaMorales Try formula like: 

 

=IF(OR(ISBLANK([question 1?]),ISBLANK([question 2?]),ISBLANK([question3?]),ISBLANK([question 4?]),ISBLANK([question 5?]),ISBLANK([question 6?]),ISBLANK([question 7?]),ISBLANK([Question 8?])),"",IF(OR([question1.?]="Inaccurate",[question 2?]="Inaccurate",[question 3?]="Inaccurate"),"Fail","Pass"))

 


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

View solution in original post