Formula Help - 2 columns and % Calculation

swhitfield
swhitfield ✭✭
edited 12/09/19 in Formulas and Functions

I am trying to give a percentage based on how 2 columns read.    Column PassFailA and Column PassFailB.   If both show Pass 100%, If A is Pass  B is Fail  or Vise Versa 50% and if both Fail 25

Tags:

Comments

  • Robert S.
    Robert S. Employee
    edited 01/19/18

    Hello,

     

    Thanks for the question. If I understand what you're looking to do correctly, this can be accomplished using a nested IF formula including the AND and OR functions. More on all of the available functions can be found here (https://help.smartsheet.com/functions), and we also have a blog post on how to build nested IF formulas which can be found here (https://www.smartsheet.com/blog/support-tip-build-nested-IF). Here's an example of how this could be written:

     

    =IF(AND([Column PassFailA]@row = "Pass", [Column PassFailB]@row = "Pass"), 1, IF(AND(OR([Column PassFailA]@row = "Fail", [Column PassFailB]@row = "Fail"), OR([Column PassFailA]@row = "Pass", [Column PassFailB]@row = "Pass")), 0.5, IF(AND([Column PassFailA]@row = "Fail", [Column PassFailB]@row = "Fail"), 0.25)))

     

    For this example I'm also using @row instead of the row's number within the cell reference. This will help make the formula more efficient as this sheet grows larger. More on @row can be found here (https://help.smartsheet.com/articles/2476491#row).

     

    I've also included a screenshot of the outcomes for every scenario you listed. I'd also like to note that if either column is left blank, this formula will leave the % Column blank until both are set to either "Pass" or "Fail".

    Screen Shot.png

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!