SOLVED

IF Formula question

%3CLINGO-SUB%20id%3D%22lingo-sub-2274738%22%20slang%3D%22en-US%22%3EIF%20Formula%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2274738%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20current%20have%20a%20formula%20I%20am%20using%20in%20SharePoint%20list%20that%20work%20great%20(see%20below)%2C%20but%20I%20need%20to%20add%20in%20additional%20if%20statements%20which%20are%20creating%20failures.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorking%20formula%3A%3C%2FP%3E%3CP%3E%3DIF(ISBLANK(%5BDate%20Completed%5D)%2C%22%22%2CIF(%5BAudit%20Rating%5D%26gt%3B79%2C%22Pass%22%2C%22Fail%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20list%20that%20has%20a%20column%20that%20displays%20a%20Pass%2FFail%20depending%20on%20the%20the%20Audit%20Rating%20listed%20in%20another%20column.%26nbsp%3B%20This%20is%20generated%20from%20a%20series%20of%20scores%20listed%20in%20additional%20columns%20and%20calculated%20into%20the%20Audit%20Rating.%20I%20want%20to%20add%20an%20additional%20statement(s)%20that%20says%20that%20if%20any%20of%20the%20columns%20of%20scores%20is%20a%20%220%22%20then%20there%20should%20be%20an%20automatic%20failure.%20Even%20if%20the%20score%20is%20above%20the%2079%20specified.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20on%20how%20to%20make%20this%20work%3F%3F%20I%20tried%20the%20formula%20below%20with%20no%20luck.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFailing%20Formula%3A%3C%2FP%3E%3CP%3E%3DIF(ISBLANK(%5BDate%20Completed%5D)%2C%22%22%2CIF(%5BAudit%20Rating%5D%26gt%3B79%2C%22Pass%22%2C%22Fail%22)%2CIF(%5B5S%20Rating%5D%3D0%2C%22Fail%22%2C%22Pass%22)%2CIF(%5BDocumentation%20Rating%5D%3D0%2C%22Fail%22%2C%22Pass%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2274738%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Lists%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

Hello,

 

I current have a formula I am using in SharePoint list that work great (see below), but I need to add in additional if statements which are creating failures.

 

Working formula:

=IF(ISBLANK([Date Completed]),"",IF([Audit Rating]>79,"Pass","Fail"))

 

I have a list that has a column that displays a Pass/Fail depending on the the Audit Rating listed in another column.  This is generated from a series of scores listed in additional columns and calculated into the Audit Rating. I want to add an additional statement(s) that says that if any of the columns of scores is a "0" then there should be an automatic failure. Even if the score is above the 79 specified.

 

Any ideas on how to make this work?? I tried the formula below with no luck.

 

Failing Formula:

=IF(ISBLANK([Date Completed]),"",IF([Audit Rating]>79,"Pass","Fail"),IF([5S Rating]=0,"Fail","Pass"),IF([Documentation Rating]=0,"Fail","Pass"))

 

6 Replies

@SLBMetrology Use below formula, it should work for you:

=IF(ISBLANK([Date Completed]), "", IF(OR([5S Rating] = 0, [Documentation Rating] = 0), "Fail", IF([Audit Rating] > 79, "Pass", "Fail")))

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.

Thank you for your help.

 

The formula you suggested did not fail, but it didn't give me the result I was looking for. When I tested it and added the 0 in for the Documentation Rating with the Audit Rating scare of 80 it did not change the status to Fail. Any thoughts??

@SLBMetrology

  1. Was the [Date Completed] blank/empty for this record/item? 
  2. What is the data type of [5S Rating]=0 and [Documentation Rating] columns? 
1. No it was not blank
2. They are 'Single line of text"
best response confirmed by SLBMetrology (Occasional Contributor)
Solution

@SLBMetrology In case of single line of text, use below formula:

=IF(ISBLANK([Date Completed]), "", IF(OR([5S Rating] = "0", [Documentation Rating] = "0"), "Fail", IF([Audit Rating] > 79, "Pass", "Fail")))
Worked perfectly!! I cannot thank you enough. I knew this would be easy for the right person. Much appreciated!! Have an amazing day!!!!