Average formula with an IF statement

jamiefico
jamiefico ✭✭
edited 04/12/24 in Formulas and Functions

I have this average formula in a sheet based on 4 columns: =AVG(Duration@row, [Existing Technical Knowledge]@row, [Technical Complexity]@row, [Testing Effort]@row, [OCM Effort]@row)


The results would be either 1,2,3, or 4. I'd like to translate the resulting number into text. For example, let's say my average formula equals 2. Instead of showing "2" in the cell it would say "Medium". Is there a way to add an IF statement as such to this formula?

Best Answers

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

    There sure is!

    =IF(AVG(Duration@row, [Existing Technical Knowledge]@row, [Technical Complexity]@row, [Testing Effort]@row, [OCM Effort]@row) = 1, "Almost Medium", IF(AVG(Duration@row, [Existing Technical Knowledge]@row, [Technical Complexity]@row, [Testing Effort]@row, [OCM Effort]@row) = 2, "Medium", IF(AVG(Duration@row, [Existing Technical Knowledge]@row, [Technical Complexity]@row, [Testing Effort]@row, [OCM Effort]@row) = 3, "Extra Medium", IF(AVG(Duration@row, [Existing Technical Knowledge]@row, [Technical Complexity]@row, [Testing Effort]@row, [OCM Effort]@row) = 4, "Super Extra Medium", ""))))

  • jamiefico
    jamiefico ✭✭
    Answer ✓

    Worked beautifully, thank you! Especially so quick on a Friday afternoon, appreciate it very much!

Answers

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

    There sure is!

    =IF(AVG(Duration@row, [Existing Technical Knowledge]@row, [Technical Complexity]@row, [Testing Effort]@row, [OCM Effort]@row) = 1, "Almost Medium", IF(AVG(Duration@row, [Existing Technical Knowledge]@row, [Technical Complexity]@row, [Testing Effort]@row, [OCM Effort]@row) = 2, "Medium", IF(AVG(Duration@row, [Existing Technical Knowledge]@row, [Technical Complexity]@row, [Testing Effort]@row, [OCM Effort]@row) = 3, "Extra Medium", IF(AVG(Duration@row, [Existing Technical Knowledge]@row, [Technical Complexity]@row, [Testing Effort]@row, [OCM Effort]@row) = 4, "Super Extra Medium", ""))))

  • jamiefico
    jamiefico ✭✭
    Answer ✓

    Worked beautifully, thank you! Especially so quick on a Friday afternoon, appreciate it very much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!