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

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 :)

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!