Home Excel Access | Links | Part two - linking the results to Excel | Alphabetical Index |

# 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

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.