Average formula with an IF statement
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
-
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", ""))))
-
Worked beautifully, thank you! Especially so quick on a Friday afternoon, appreciate it very much!
Answers
-
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", ""))))
-
Worked beautifully, thank you! Especially so quick on a Friday afternoon, appreciate it very much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!