Sum Value Across Multiple Rows

I need to confiruge an average score between mulitple rows. The row value are determined by this formual

=IF([Question 1]@row = "Strongly agree", "5", IF([Question 1]@row = "Somewhat agree", "4", IF([Question 1]@row = "Neither agree nor disagree", "3", IF([Question 1]@row = "Somewhat disagree", "2", IF([Question 1]@row = "Strongly disagree", "1")))))

Therefore, I think I need to use a value fuction for it to add these as numbers. What formual can I use to get an average for columns 1 Score, 2 Score, 3 Score, and 4 Score?

Find more posts tagged with

Accepted answers

Kerry St. Thomas

As an aside, if you modify your formula to remove the quote marks around numbers e.g.
=IF([Question 1]@row = "Strongly Agree", 5, IF(….
the formulas will result in a value in the first place and you won't need to use VALUE for aggregation.

With that cleanup, the formula to get your average for all of Q1 scores would be fairly straightforward:
=AVG([1 Score]:[1 Score])

This formula could be on your Sheet Summary, or you could create a KPI sheet of sorts, that has cross-sheet references for each of Q1 average, Q2 average, etc.

Good luck, and happy Smartsheeting!

All comments

hjgeorge

Solved: I added helper columns and used =VALUE([1 Score]@row ) to pull the number value. Then I was able to use the helper columns to get the sum and average of the set.

Kerry St. Thomas

This formula could be on your Sheet Summary, or you could create a KPI sheet of sorts, that has cross-sheet references for each of Q1 average, Q2 average, etc.

Good luck, and happy Smartsheeting!

[Deleted User]

@hjgeorge If your scores are stored as text ("5","4"...), use:
=AVG(VALUE([1 Score]@row ), VALUE([2 Score]@row ), VALUE([3 Score]@row ), VALUE([4 Score]@row ))

Better option (recommended): return numbers (5,4,3…) in your IF formula, then use:

=AVG([1 Score]@row , [2 Score]@row , [3 Score]@row , [4 Score]@row )

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.