Section 5 – Examples Using the Accompanying Excel Spreadsheet

April 27, 2012

The accompanying Excel spreadsheet implements relevant calculations using both normal approximations and the binomial. This spreadsheet was developed using Microsoft Excel 2007.

Notice – This spreadsheet is an .xlsm, meaning that it contains VBA code (macros). You may have to adjust your security settings in order to view and use them.

Caveat – This spreadsheet is intended to assist in learning. EDRM does not warrant the accuracy of this spreadsheet.

5.1. Pages

There are six pages.

  • Notes. The same descriptive information that appears here.
  • Normal Approx – Basic. Demonstrates use of the normal approximation of the binomial to solve for confidence level, margin of error and sample size in terms of each other. These calculations are based on the simplified, conservative assumption that underlying probability = 0.50.
  • Normal Approx – Obs. Demonstrates how greater accuracy can be obtained when the sample result (number of observed successes or estimate of success rate) is also known or can be provided.
  • Normal Approx – Pop. Actually uses the normal approximation of the hypergeometric to demonstrate the effect of underlying populations sizes.
  • Binomial Methods – Basic. Uses binomial techniques to solve for confidence level, margin of error and sample size in terms of each other. These calculations are based on the simplified, conservative assumption that underlying probability = 0.50. (As noted on the spreadsheet, the specific binomial technique is a modification of an approach known as the Clopper-Person technique.)
  • Binomial Methods – Obs. Demonstrates how greater accuracy can be obtained when the sample result (number of observed successes) is also known or can be estimated. Note also that, instead of solving for margin of error, this solves for the low and high endpoints of confidence range — due to the absence of symmetry.

5.2. Usage, Color Coding and Functions

Here is some information regarding the use of this spreadsheet:

  • Columns with yellow headers are inputs. These can be modified to see results in the other columns.
  • Columns with green headers implement the solution using standard Excel functions. Do not modify these columns unless you specifically intend to modify the logic or implement alternative logic. In some cases, where indicated, standard Excel functions do not exist that will directly solve the problem.
  • Columns with blue headers implement the solution using Visual Basic for Application (VBA) functions, i.e., “macros”. Do not modify these columns unless you specifically intend to modify the logic or implement alternative logic. In some cases, these reproduce what standard Excel can do, but the functions are easier to use. In other cases — where iterative search is required — these are needed because no standard Excel functions are available. Those interested can read the VBA code to see the logic.