Column Formula: Work On Some Rows and Not Others

Kal · 06/13/22

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

Genevieve P. · 06/14/22

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

Kal · 06/14/22

https://community.smartsheet.com/discussion/comment/332827#Comment_332827

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.

Genevieve P. · 06/14/22

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

Kal · 06/14/22

@Genevieve P. Same Issue when I use your Formula.

Kal · 06/14/22

https://community.smartsheet.com/discussion/comment/332807#Comment_332807

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.

Genevieve P. · 06/14/22

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

Kal · 06/14/22

https://community.smartsheet.com/discussion/comment/332827#Comment_332827

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.

Genevieve P. · 06/14/22

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!

Kal · 06/14/22

https://community.smartsheet.com/discussion/comment/332827#Comment_332827

Thank You Very Much!

Column Formula: Work On Some Rows and Not Others

Best Answers

Answers

Help Article Resources

Categories