Analysing survey results within a Google worksheet

 Google Docs include a reasonable spreadsheet which can be used to summarise the results from a Google form.

This article does not explain about creating a form and it focuses on processing the results. I have described how the results can be automatically picked up within Excel. It is also possible to analyse or summarise them in the Google worksheet and then publish them to the web so that anyone (who knows the URL address) can view a 'live' summary.

Note that it only appears to be possible to publish a single part (or all) of a document to the web. If you publish the results to a text file on the web, you can't also publish a section of a worksheet as a summary in HTML format.

The following section is a live analysis (from Google) of the above survey, embedded within this page. I selected the options to publish a particular worksheet AND a named data range. Quoting a range name (see Data | Named and Protected Range) rather than a simple range (e.g A1:G20) appears to prevent the iFrame from scrolling to other areas of your worksheet which you may wish to keep private. If users complete more survey responses. this iFrame content will update automatically.

The four cells which have been shaded in pink relate to the formulas shown below. Although most formulas use identical functions to Excel, there is a big difference with array formulas. The Excel SUMPRODUCT function is not available in a Google spreadasheet and therefore a conditional sum of values must be acheived using an ARRAYFUNCTION.

1	=if(isna(match(A6,'Form Responses'!$B$2:$B$18,0))=true,"No response","OK")
	Col B - if an attempt to MATCH the ward name in the Form Responses list fails, an ISNA error will occur. This will identify wards who have not responded,
2	=if(B6="OK",vlookup(A6,'Form Responses'!$B$2:$D$18,2,0),0)
	Col C - if the survey has been completed, VLOOKUP the ward name in the Form Responses and return the adjacent value of the number of qualified nurses.
3	=right("-----" & rept("x",if(E8<F8,F8-E8,0)),5)
	Col H - create a short graphical string which will include an 'x' if the Total Staff numnber is less than the Establishment. This shows the extent to which wards are understaffed
4	=arrayformula(sum(if(iserr(SEARCH(A17,'form responses'!$E$2:$E$20)),0,1)))
	Col B - count the number of times in which the word Agency occurs in the list of Form Responses. If the word is not found in each cell within the array, the search creates an error and a value of 0 will be returned. Each time it is found, 1 is added to the sum.
5	=arrayformula(sum(if(F4:F13<E4:E13,1,0)))
	This formula is not contained in the displayed worksheet and is used to produce a value for the chart. It will count the number of records in which the Establishment is less than the Total Staff - i.e. the ward is overstaffed.

In Excel the ARRAYFORMULA function would not be used, but the remainder of the array formula could be entered using Control Alt Delete. Alternative this could be acheived more simply in Excel using SUMPRODUCT.

Pre-populating fields for each user

If you are able to customise the hyperlink which you give to each user, the form can open with particular fields already completed. For example in the previous survey, each of the ward managers could be sent a unique hyperlink which automatically populate the appropriate Ward Name field.

Open the Google document containing the form and select the menu option to Edit Form. Within the edit screen select Responses | Get pre-filled URL. This will display a view of the form which allows you to enter particular responses. After you click [submit] the modified URL will be displayed - appended with some 'entry' items.

Note that spaces within a text string must be replaced with a '+' (plus) character.

This page is not currently included within the Excel functions guide

Click here for details about obtaining this file