Report not summarizing where values are generated from a logic..

Dave from JBM

Not sure if this is doable or not but I am trying to create a report that will summarize by averaging the data fields where the number is being returned by an IF statement.

The problem is where my summary number should be in the report im just getting "NaN".

In the original sheet several responses have been collected where the response options are, Strongly Agree, Agree, Somewhat, Disagree, and Strongly Disagree. Next to each of the response columns I have another column that is turning those responses into a numerical value. "Strongly Agree" returns "5", "Agree" returns "4", etc.

even if I try to average at the bottom of the sheet it doesn't seem to recognize those values where the number is being generated by the statement.

Ultimately I'm trying to get to a dashboard where I will show a score which is the average of the response value for each question.

Any help would be greatly appreciated :)

Paul Newcome

Remove the quotes from around your numbers in the IF formula. When you wrap in quotes like that, you are actually generating a text value as opposed to a number value. Removing the quotes should clear that up for you.

Heather Duff

Hi Dave,

Can you please provide screenshots (with any sensitive information blocked)? That may help with determining what the issue is. It would also be helpful to see what formulas you are using.

Thanks,

Heather

Dave from JBM

https://community.smartsheet.com/discussion/comment/283239#Comment_283239

This first one is from my sheet and you can see the formula there.

then this one is from the report where I was trying to summarize with the avg.

Thanks!

Paul Newcome

Remove the quotes from around your numbers in the IF formula. When you wrap in quotes like that, you are actually generating a text value as opposed to a number value. Removing the quotes should clear that up for you.

Dave from JBM

https://community.smartsheet.com/discussion/comment/283585#Comment_283585

Thanks Paul, that was it. You are the man!

Paul Newcome

Happy to help. 👍️