Column Formula: Work On Some Rows and Not Others
Smartsheet Community:
I have a Column Formula and it is Not Working Constantly. Take a look at the Empty Cells in Screenshot. I have also Added the Formula Below for Each Column Formula:
Average Pain Formula: =AVG([No Pain]@row:[Extreme Pain]@row)
Overall Pain Level Formula: =IF([Average Pain]@row = 0, "No Pain", IF([Average Pain]@row = 1, "Mild", IF([Average Pain]@row = 2, "Moderate", IF([Average Pain]@row = 3, "Severe", IF([Average Pain]@row = 4, "Very Severe", IF([Average Pain]@row = 5, "Extreme"))))))
Overall Emoji Pain Formula: =IF([Overall Pain Level]@row = "No Pain", "No Pain", IF([Overall Pain Level]@row = "Mild", "Mild", IF([Overall Pain Level]@row = "Moderate", "Moderate", IF([Overall Pain Level]@row = "Severe", "Severe", IF([Overall Pain Level]@row = "Very Severe", "Very Severe", IF([Overall Pain Level]@row = "Extreme", "Extreme"))))))
Screenshot:
Thanks
Best Answers
-
Hi @Kal
Ah! I've just realized that the AVG is likely adding decimals to your numbers, so "5" is likely "5.5" or something similar (you can test this by adjusting the formatting on that column to show decimals from the top toolbar). Your formula is looking to see if that cell " = 5" which would only look for the round number.
Try wrapping a ROUND Function around your Average to make sure it only outputs a round number:
=ROUND(AVG([No Pain]@row:[Extreme Pain]@row))
This should sort it out!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
That is the Problem, even though I have not Tried it yet. I Formatted The Cell so the Decimals would not Show. I will make the Necessary Adjustments as you Recommended and let you know if that Resolved my Issue.
Answers
-
Hi @Kal
It sounds like the formula isn't recognizing certain values in the Average Pain column as numbers.
Could you try wrapping a VALUE function around your AVG formula?
Try:
=VALUE(AVG([No Pain]@row:[Extreme Pain]@row))
It would also be helpful to see your columns from [No Pain]@row:[Extreme Pain]@row, with an explanation of how the values are entered.
On another note, you could simplify your Emoji Column to simply be
=[Overall Pain Level]@row
Since your text values match the Emoji text, you don't need to re-write it in the formula.
Let me know what happens with the Average formula!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. Same Issue when I use your Formula.
-
Columns from [No Pain]@row:[Extreme Pain]@row are being Cross Referenced from Another Sheet to get the Count.
Same Issue when I use your Formula.
-
Hi @Kal
Ah! I've just realized that the AVG is likely adding decimals to your numbers, so "5" is likely "5.5" or something similar (you can test this by adjusting the formatting on that column to show decimals from the top toolbar). Your formula is looking to see if that cell " = 5" which would only look for the round number.
Try wrapping a ROUND Function around your Average to make sure it only outputs a round number:
=ROUND(AVG([No Pain]@row:[Extreme Pain]@row))
This should sort it out!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
That is the Problem, even though I have not Tried it yet. I Formatted The Cell so the Decimals would not Show. I will make the Necessary Adjustments as you Recommended and let you know if that Resolved my Issue.
-
Ok great! I'm certain this is it, so let me know if there are any additional complications that come up.
Even though the column is formatted not to display the decimals, they still exist as content in the cell. Adding a ROUND function to your formula ensures that the actual number being output doesn't have any decimals at all.
I hope you have a good day!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank You Very Much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!