power4XL Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins & More!

Normal distribution worksheet approach - results and histogram

ShareThis

Finally we analyze and summarize our results as illustrated in columns G - J below.

In order to build our histogram we create the "Histogram Data" table.  The "# Scores" cells uses the CountIf() function to tally the number of results in each bracket. The formula in each of those cells is:

 

=COUNTIF($E$20:$E$2519,G31)

 

where $E$20:$E$2519 is the range of output data, and the reference to G31 changes appropriately for each row of the histogram data table. The "% Scores" column uses the following formula in each cell:

 

=I30/SUM($I$26:$I$44)

 

adjusting the I30 reference appropriately, of course, for each instance.

Below the table we created the bar chart histogram using the table as our underlying data.

 

We constructed this model with 2500 iterations.  Running the model repeatedly (by pressing F9), we observe a great deal of variability in results, so it would take a great many more iterations to have consistency.  That makes the problem an ideal candidate for the VBA approach, which we will demonstrate next.

 

Prev | Contents | Next

Copyright notice:  This site and all content, including computer code and spreadsheet examples, are copyright 2006 by Fritz Dooley.  License is granted for individual users to download examples and to copy code directly into user's spreadsheets and Visual Basic for Applications files.  Users may not redistribute code in any way.  Providing hyperlinks to this web site is encouraged, but posting code and examples on other web sites is expressly forbidden.  "Microsoft" is a registered trademark of Microsoft Corporation.   Neither this web site nor Fritz Dooley is affiliated with Microsoft Corporation.