Average calculation on survey AVG(Collect)?

Sharon Blanchard

We have a survey form for multiple programs with 20 criteria for rating/scoring. I want to create a separate summary sheet that shows each Program Name, and the average score for each criteria. Please advise the best cross sheet formula to use for this.

Kelly Moore

Yes Sharon,

AVG(COLLECT) is the formula you need. It has the syntax AVG(range to be averaged, range1, criteria1, range2, criteria2, range3, criteria, etc). You can add as many or as few criteria as needed as long as they are added in range-criteria pair.

If you need any assistance with your cross sheet references, let us know.

Kelly

Sharon Blanchard

Hi Kelly,

This is the formula I've tried and it's not working (I am getting a #divide by zero error) I have a Program column and a Trust column on another sheet, and I want to calculate: If the program name is Johnson and Johnson, what is the average score for trust.

=AVG(COLLECT({Program}, {Trust1}, OR(@cell = "Johnson and Johnson", @cell > 0)))

I'm thinking that I'm doing something wrong with the Program name because I dont' want the average of that. Thanks so much if you can assist!

Sharon