Formula won't recognize values as numbers
Hi there!
Problem = I have four "Text/Number" columns that I want to average
Complexity = the column values are derived from formulas
Issue = when I try to average all four columns with =AVG([Stakeholder Score]@row:[Complexity Score]@row) formula, it keeps throwing an error. Even when I try the long way around =SUM([column1]@row:[column4]@row)/4, I get zero.
I feel like it's not recognizing the "Scores" as number values. Help!
Best Answers

I used the same formulas, used them against text/number columns with column formulas, and had no trouble. Text auto aligns left. Numbers auto align right. If you haven't set your cell alignment up yet, then by the looks of it yours is coming in as text (string) instead of numbers. I'd be interested in seeing one of the column formulas in your 4 columns that is causing the values to be text instead of numbers.
You can use the Value() function to convert text to number.

@Ray Lindstrom this was very insightful! I had no idea the value alignment in the cell told you anything. The formula for the columns is a nesting IF formula based on values in another column to create scores. =IF([column name]@row = "value1", "1", =IF([column name]@row = "value2", "2", =IF([column name]@row = "value3", "3")))
Would you be able to help me figure out how to convert it into a number from text?

@Ray Lindstrom I found an article that showed me how to use the VALUE() formula and it worked! Thank you so much for your help!!!

Awesome news! So glad it worked for you. Thanks for the update.
Answers

I used the same formulas, used them against text/number columns with column formulas, and had no trouble. Text auto aligns left. Numbers auto align right. If you haven't set your cell alignment up yet, then by the looks of it yours is coming in as text (string) instead of numbers. I'd be interested in seeing one of the column formulas in your 4 columns that is causing the values to be text instead of numbers.
You can use the Value() function to convert text to number.

@Ray Lindstrom this was very insightful! I had no idea the value alignment in the cell told you anything. The formula for the columns is a nesting IF formula based on values in another column to create scores. =IF([column name]@row = "value1", "1", =IF([column name]@row = "value2", "2", =IF([column name]@row = "value3", "3")))
Would you be able to help me figure out how to convert it into a number from text?

@Ray Lindstrom I found an article that showed me how to use the VALUE() formula and it worked! Thank you so much for your help!!!

Awesome news! So glad it worked for you. Thanks for the update.
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives