Am I using the AVGW formula correctly? Can I make it a column formula?

sheetsmartsarah

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) + ([None-CNT-Program Id]@row * [None-CNT-Program Id]1) + ([Not Applicable-CNT-Program Id]@row * [Not Applicable-CNT-Program Id]1)) / [Grand Total-CNT-Program Id]@row

1. Am I using the AVGW formula correctly? Or is my manual formula more accurate?
2. 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!

JamesB

@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.

JamesB

@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.