These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Tuesday, April 14, 2015

Survey Statistics Recoder

Executive Summary

Lindon City is located just north of Orem, and has a population size of roughly 10,500 residents. Lindon is a unique city in that it is a full-service city, despite its small size. A full-service city is a city which provides all of the services needed to operate a city, including fire and police services. Lindon has a significant commercial tax base which allows it to provide more services than other cities of similar size.

To help measure their service satisfaction levels, the city put out a service satisfaction survey to those who interact regularly with members of the city staff. The survey was placed on the front desks of various departments in the City, and as visitors converse with a member of the city staff, they are asked if they would provide some feedback about their experience in the survey.

As these survey responses are collected, the City Administrator asked me if I would come up with a way to analyze our collected responses. To do this, I created an Excel model where survey responses can be entered into Excel through help of a form. The user can then run statistics on the data by clicking a button which will automatically recode the data and output some statistics.

Since most of the surveys taken are paper surveys, I tried to create an interface that would make input into the Excel spreadsheet as easy as possible. When the user clicks on the “Input Form” button, a user form pops up. The user form allows users to simply click the responses reported on the survey, and once the user clicks “Enter,” the appropriate fields are automatically populated in the spreadsheet. Once the user is finished inputting survey responses, the user can close the form by clicking “Close.”

Once the user is ready to analyze the data, the user can click the button “Statistics.” This button runs a macro that recodes the data into a format that can be analyzed, and then a statistical analysis is displayed in the spreadsheet. The macro works by looking at each of the values that were input into the spreadsheet and assigns each value a specified number. These numbers correlated to a typical Lickert Scale. Once each response is recoded, the recoded numbers are populated in a separate spreadsheet. The macro then opens the statistical analysis package included in the Excel software, and selects the appropriate ranges to be analyzed. The macro then runs the summary statistics package for each selected range in the recoded worksheet and places the outputs in a specified worksheet to be displayed to the user.

No comments:

Post a Comment

Blog Archive