mytest > help > Part 2. MM4XL Tools > 1. Strategic Tools > Risk Analyst > 4. Functions > 2. Utility Functions > mmHISTO(InputRng, [Optional: Classes])

Risk Analyst

mmHISTO(InputRng, [Optional: Classes])

This is an array function that returns the number of elements by class of a column of data. It can be used to produce the data needed to draw a histogram chart like that drawn for output variables with Risk Analyst.

An array formula is entered with Ctrl+Shift+Enter, and it requires selecting a large enough range of cells in order to print the whole results.

The function takes two arguments.

InputRng is the range on sheet containing the data to be classified. In the picture below this would be B20:B5018.

Classes an optional argument that tells the function the number of classes that the input data has to be grouped into. When omitted, the argument is set by default to a number of bins according to the solution described in the ASTM manual:

 Monte Carlo Simulation Software: Management Process Risk Analysis

Example

We have 5000 simulations and would like to draw a histogram with a different number of classes than that produced by Risk Analyst. For example, we would like to have the chart split into 15 classes rather than the 11 produced automatically.

 Monte Carlo Simulation Software: Management Process Risk Analysis

Referring to the data in the picture above (mind the hidden rows), we select with the mouse the whole range D1:G15 and enter the following formula in one of the cells, we did so in cell D1:

=mmHISTO(B20:B5018, 15)

Then, rather than pressing Enter as usual, we press simultaneously Ctrl+Shift+Enter. The picture below shows the result of the function in 15 classes.

 Monte Carlo Simulation Software: Management Process Risk Analysis

Column D contains class labels, in column E we see the upper class boundary, column F displays the number of items by class, and column G shows the cumulative percent values after each class. In the picture above, in row 1, class 1 accounts for 564 items with a value of 12.6 or less, which account for 11% of the 5000 trials. Class 5 has 480 items with a value less than or equal to 22.9 and larger than 20.3, and so on. The data in column F and G can be used to draw a new histogram as in the picture below:

 Monte Carlo Simulation Software: Management Process Risk Analysis

A question that often comes up when dealing with histogram charts is How many classes should be used? mmHISTO by default defines the number of bins according to the solution described in the ASTM manual. However, there is no fixed rule, and the user is free to choose the solution they feel is appropriate.

Lifetime license:

Price: euro

Vote this tool
We proudly serve
Your vote
vote1 vote2 vote3 vote4 vote5
Email:
Gender:
M
F
Age:
Position:
Department:
Comment: