Help on IF formula... validating two columns.

Hi, hope everyone is doing well? hoping someone could please help me with this formula, at the moment I am using the below formula to check the % from Score column and give the outcome result in the Result column and this is working fine, just wondering, if there a way I can include ‘Auto Fail’ in the above formula so if Autofail column is ‘Y’ then it will show Result as ‘Auto Fail’ in the Result column?

Many Thanks,

=IF([Score]@row < 0.5, "Fail", IF(AND([Score]@row > 0.5, [Score]@row < 0.65), "Pass", IF(AND([Score]@row > 0.64, [Score]@row < 0.75), "Credit", IF(AND([Score]@row > 0.74, Score]@row < 0.85), "Distinction", IF(AND([Score]@row > 0.84, [Score]@row <= 1), "High Distinction")))))

Ex: In the above table, Row2 and Row5 Autofail column is 'Y' so i want the Result to show as 'Auto Fail' in Result column.

Score <50% = Fail

Score >50% and <65% = Pass

Score >64% and <75% = Credit

Score >74% and < 85% = Distinction

Score >84% and <=100% = High Distinction

Autofail = ‘Y’ then Auto Fail to show in Result column.

Best Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    Try this one:

    =IF([Autofail]@row = "Y", "Auto Fail", IF([Score]@row < 0.5, "Fail", IF(AND([Score]@row > 0.5, [Score]@row < 0.65), "Pass", IF(AND([Score]@row > 0.64, [Score]@row < 0.75), "Credit", IF(AND([Score]@row > 0.74, Score]@row < 0.85), "Distinction", IF(AND([Score]@row > 0.84, [Score]@row <= 1), "High Distinction"))))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    This should work for you:

    =IF(Autofail@row = "Y", "Auto Fail", IF(Score@row< 0.5, "Fail", IF(Score@row< 0.65, "Pass", IF(Score@row< 0.75, "Credit", IF(Score@row<0.85, "Distinction", "High Distinction")))))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!