What formula do I use for Grading?

Options
Nate420
Nate420 ✭✭
edited 09/13/22 in Formulas and Functions

I have a sheet used for grading our product. There is a final score for each product out of 36. I would like to use a formula that uses the final score to give the product a "Grade".

0-12 = C, 13-23 = B, 24-29 = A, 30-36 = A+

How do I set up a formula to kick out a letter grade in a cell depending on a range of numbers in another cell in the same sheet?

Tags:

Best Answer

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    edited 09/13/22 Answer ✓
    Options

    Hi @Nate420

    Try this and let me know if it works for you:

    =IF([Final Score]@row = 0, "C", IF([Final Score]@row < 13, "C", IF([Final Score]@row < 24, "B", IF([Final Score]@row < 30, "A", IF([Final Score]@row < 37, "A+", "False")))))

    If any grade exceeds 36 the word "False" will populate. You can replace "False" in the quotations reflect anything you wish, or add to the formula.


Answers

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    edited 09/13/22 Answer ✓
    Options

    Hi @Nate420

    Try this and let me know if it works for you:

    =IF([Final Score]@row = 0, "C", IF([Final Score]@row < 13, "C", IF([Final Score]@row < 24, "B", IF([Final Score]@row < 30, "A", IF([Final Score]@row < 37, "A+", "False")))))

    If any grade exceeds 36 the word "False" will populate. You can replace "False" in the quotations reflect anything you wish, or add to the formula.


  • Nate420
    Nate420 ✭✭
    Options

    @Mr. Chris This worked perfect thank you.

    I would also like to add a formula to the same sheet that shows a Pass or a Fail depending on three other columns.


    If Mold Present column say "No" & the Seeds per 100g column says "None" or & the Foreign Matter column says "No" mark the cell as a PASS. If Mold Present column says "Yes - Beige" or "Yes-Grey" or "Yes-Black" & the Seeds per 100g column says "<5" & the Foreign Matter column says "Yes" mark the cell as FAIL.

    Is this possible?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!