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
-
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!
Answers
-
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
-
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!
-
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!
-
Thanks Paul, that was it. You are the man!
-
Happy to help. 👍️
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!