StDEvA With Collect Across Multiple Collumns

Tom Salmond

We have a form collecting survey results at a conference we are running. The columns we have listed are below (there are more than this but this gives an idea):

I need to run a STDevA of all the column results based on the session name (column 1).

On a separate sheet I tried to create the following formula:

=STDEVA(COLLECT({All Results}, {Session Column}, Session@row))

All results was a selection of all the columns above with numbers
Session Column selects the Session column above
Session@row is a cell that contains the session name that I am using as my criteria

It works if I reference a single column, but if I pick a range of columns it does not work… and I cannot figure out how to proceed. I need to select all the numerical data in the rows that match the session ID and calculate a standard deviation.

Tom Salmond

I found a solution that worked. I simply created a helper column for each individual column that calcuated its StDevA and squared it:

=IFERROR(STDEVA(COLLECT({Instructor Skill}, {Session Column}, Session@row)) ^ 2, 0)

Note: I had to square the result in order to add them in the following step

I then used another helper column to add them together:

=SUM([Range of All St. Dev Helper Columns])^0.5

That resulted in a single StDevA for each session that I could use to calculate a confidence interval

Tom Salmond

To clarify one point, when I mentioned that it fails when I reference a range of columns, I am referring to the {All Results} portion of the above critera (the Collect range).

Tom Salmond

I found a solution that worked. I simply created a helper column for each individual column that calcuated its StDevA and squared it:

=IFERROR(STDEVA(COLLECT({Instructor Skill}, {Session Column}, Session@row)) ^ 2, 0)

Note: I had to square the result in order to add them in the following step

I then used another helper column to add them together:

=SUM([Range of All St. Dev Helper Columns])^0.5

That resulted in a single StDevA for each session that I could use to calculate a confidence interval