What it is
As part of a project for my sabbatical I collected quite a lot of data in the form of evaluations of questions on a 15 scale  a form that is commonly called a Likert scale.
I wanted to determine if the opinions expressed were "significant". To do so I made the following assumptions:
 The null hypothesis is that the results will be spread approximately evenly about the middle of scale (3 in my case), which was always expressed as a neutral result between extremes such as "approve strongly" and "disapprove strongly".
 One can model the distribution using the "normal" model
In order to analyze the results I followed an approach given in "Stats: Data and Models" by De Veaux, Velleman, Bock (2nd Edition) for categorical data, the "Goodness of Fit" model.
In essence I predicted the number of counts in each of the cells using the normal model with a standard deviation the same as that of the real data, then applied the Goodness of Fit test to develop a probability. If the probability that the null model fit the data was less than 5% the null hypothesis could be rejected  the data was significantly different.
 Note that "significantly different" could be multimodal, not just a higher or lower mean.
What the VBA Code Does
Input Assumptions
 Your data are arranged in five cells horizontally with a count of the number of responses in each cell as in the :"input" example line below  the top line is not necessary.
 Your "active cell" is somewhere to the right of those data cells on the same line. In this case in the red cell in Col6, but it could be in Col7 or Col8
 You have as many rows of data as you wish, each to be analyzed separately
Input

Col1 Col2 Col3 Col4 Col5 Out1 Out2 Out3
4 8 16 8 4 4 4 20 15 4 0 4 22 16 4 3 2 6 3 2 3 15 18 22 6  Once you have the macro active (you need to import to a VBA module for this). Run the macro "Likert Goodness of Fit". It will explain what's going to happen and then ask for
 The number of rows you want to calculate
 The "offset" you want. The default is 0 which assumes your "active cell" is right next to the data.
 If you want an empty column put the cursor in Col7 and enter "1" for the offset.
Results

Col1 Col2 Col3 Col4 Col5 Out1 Out2 Out3
4 8 16 8 4 3.0 91.58% 4 4 20 15 4 3.2 0.51% 0 4 22 16 4 3.4 0.10% Adjusted for zero cell 3 2 6 3 2 2.9 Cells <25 3 15 18 22 6 3.2 28.26%
Interpreting the results
 Columns 15 are unchanged  your data
 Out 1 is the weighted average of the Likert values (15 scale)
 Out 2 is PValue that the data can be explained by the null hypothesis.
 Out 3 has a comment if the analysis has made a change or found the data lacking
 "Cells <25" means that the total number of responses was less than 25, a reasonable lower bound for this analysis to have meaning.
 "Adjusted for zero cell" means that the data was "adjusted" during the analysis to prevent the analysis from "blowing up" due to a division by zero because of zero responses in a cell. This adjustment is accomplished by adding "1" symmetrically to the zero cell and the one one in the same position on the other side of the center (if the center is zero "2" is added there).
 I believe from testing and analysis that this is a conservative approach, but I have not proven it statistically. I welcome someone who wishes to do so.
 Unshaded results are "significant"
 Shaded results fail the significance test or have less than 25 responses
Modifying the Code
 The code is fairly extensively commented and is broken down into multiple sub procedures to enable easy modification.
 There are also provisions for readily changing it by setting a single variable to other Likert Scales  odd numbers  I haven't tested even one.
 It's also easy to add other columns of output
Feedback
If you use this code I'd greatly appreciate some feedback on whether it was helpful. Even more important, if you find errors I'd like to know of them so I can fix them.
I have quite a few other Architectural Engineering resources including a link (under Excel) to this page.
Jim Mitchell  Drexel University