Am I using the AVGW formula correctly? Can I make it a column formula?
Hi, I'm trying to calculate the weighted average for a survey question. The question asks if the person was satisfied with the learning program they attended.
I want to have the score for this question for each program to identify the programs that were highly satisfactory, our "top rated" programs.
I've created a Pivot sheet from the survey responses (9,000+ responses). I have the programs/rows grouped by topic. With the survey response options as my column headers. Each cell has the count of responses for that option.
I added a Weighted Average column and included the weights on the first row.
For the yellow highlighted cell, I have the following formula:
=AVGW([Agree Strongly]@row:[Not Applicable]@row, [Agree Strongly]1:[Not Applicable]1)
I've searched the community and help articles. Also, referenced the formula handbook so I think I'm entering the formula as expected, I just thought that this formula would return a score no greater than 5 based on my Google search results.
For the pink outlined cell I have this formula:
=(([Agree Strongly]@row * [Agree Strongly]1) + ([Agree Somewhat]@row * [Agree Somewhat]1) + ([Neither Agree nor Disagree]@row * [Neither Agree nor Disagree]1) + ([Disagree Somewhat]@row * [Disagree Somewhat]1) + ([Disagree Strongly]@row * [Disagree Strongly]1) + ([NoneCNTProgram Id]@row * [NoneCNTProgram Id]1) + ([Not ApplicableCNTProgram Id]@row * [Not ApplicableCNTProgram Id]1)) / [Grand TotalCNTProgram Id]@row
 Am I using the AVGW formula correctly? Or is my manual formula more accurate?
 Can I make an AVGW formula a column formula? I'm getting a Syntax error when I attempting to convert either formula. Don't want to cut+paste 200+ times.
Thanks for any and all help!
Best Answer

@sheetsmartsarah You cannot make column formulas when calling out an individual cell.
i.e. [Agree Somewhat]1 vs [Agree Somewhat]@row.
To get around this use your sheet summary area and create the same column names with the weighted number you want to multiply by. Then in your formula, change it from [Agree Somewhat]1 to [Agree Somewhat]#
This will allow you to make it a column formula.
Answers

@sheetsmartsarah You cannot make column formulas when calling out an individual cell.
i.e. [Agree Somewhat]1 vs [Agree Somewhat]@row.
To get around this use your sheet summary area and create the same column names with the weighted number you want to multiply by. Then in your formula, change it from [Agree Somewhat]1 to [Agree Somewhat]#
This will allow you to make it a column formula.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!