Formula won't recognize values as numbers

Layla Morales

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!

Ray Lindstrom

@Layla Morales,

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.

Layla Morales

@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?

Layla Morales

@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!!!

Ray Lindstrom

@Layla Morales,

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

Ray Lindstrom

@Layla Morales,

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.

Layla Morales

@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?

Layla Morales

@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!!!

Ray Lindstrom

@Layla Morales,

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