Skip to content

Home / Monte Carlo / Model Building / Custom Distributions

Custom Distributions

From Excel click...

QXL Monte Carlo New > Design > Create/Modify Design Sheet > Custom Distributions

Builds a distribution of your own shape, stored in the workbook, that you can then pick when marking an input just like a built-in one.

Use it when the quantity you are modelling does not have the shape of any built-in distribution: a bimodal supply, a screened part with a hole in the middle of its range, or a set of outcomes with weights you know.

A custom distribution belongs to the workbook it was built in. It travels with the file, and it is offered in every place the distribution list appears in that workbook.

The two types

Continuous. A run of segments. Each row is one segment, given as X Low, X High and Relative Probability. A trial picks a segment according to the weights, then draws a value evenly from anywhere inside it, so the distribution is flat within a segment and steps between segments.

Segments may touch at an edge but may not overlap. A gap between two segments is allowed, and it is a range the distribution never produces, which is how a screened part with a hole in its range is described.

Discrete. A set of separate values. Each row is one outcome, given as X and Relative Probability. A trial picks one of the listed values according to the weights, and produces nothing else. Each X must appear only once.

Relative Probability is a weight, not a percentage. The values do not have to add up to 1, or to 100, or to anything in particular. They are read against each other, so 1, 2, 1 and 25, 50, 25 describe the same distribution. Every weight must be greater than zero.

The dialog

The list at the top chooses what you are editing. Its first entry is Add new..., and the stored distributions of this workbook follow it. Choosing one loads it for editing.

The rest of the dialog is the editor on the left and a live density preview on the right.

Name and type

Name. What you will pick when marking an input. Three names are refused:

'Normal' is a built-in distribution name; a custom distribution with this name could never be used. Choose a different name.

'Empirical' is reserved for worksheet Empirical variables. Choose a different name.

A custom distribution named 'Supply' already exists. Choose a different name, or select that distribution and edit it.

Editing a distribution and keeping its own name is not a duplicate, and neither is changing only its capitalisation.

Type. Continuous (X Low, X High, Relative Probability) or Discrete (X, Relative Probability). The choice sets the columns of the grid below.

Data

A grid with one row per segment or outcome, and a ✕ button on each row to delete it.

Four buttons build the grid:

Button What it does
Add row adds one empty row at the bottom
Import from range... asks you to select a worksheet range with Excel's own range picker, and loads it. Three columns for Continuous, two for Discrete
Paste loads rows from the clipboard, so data can come from anywhere that copies as text
Export to new sheet writes the grid to a new worksheet, header row in bold. Refused while the grid has a problem: Fix the highlighted problems before exporting.

Rows are added by pressing Add row. The grid does not grow a new row by itself as you type in the last one.

Fit from data

Fit from data... builds a Continuous distribution from a column of raw values rather than from rows you type. It asks you to select the data with Excel's range picker, then fills the grid with the segments it found, and reports what it did:

Fitted 12 segments from 480 values.

Bins sets how many bins the fit uses. Leave it blank for the automatic count, which is chosen by the Freedman-Diaconis rule, or type a whole number to fix it. Anything else is refused:

Enter a whole number of bins, or leave the Bins box blank for automatic.

Only numeric cells in the selected range are used. Blanks and text are ignored.

This is not the Mark Input button of the same name

The Fit Distribution from Data button on the Mark Input dialog is an advertisement for a future version and does nothing yet. Fit from data here is wired and works. The result is a custom distribution, which you then choose by name when marking the input.

Density preview

The panel on the right draws the distribution the grid currently describes, and redraws as you edit.

What the grid must contain

A blank row is ignored rather than being an error, so a half-finished row at the bottom does not block anything. Every row that has anything in it is checked:

Problem What it says
X Low, X or X High is not a number X Low must be a number.
a Continuous row with the ends the wrong way round X High must be greater than X Low.
Relative Probability is not a number Relative Probability must be a number.
Relative Probability is zero or negative Relative Probability must be greater than zero.
nothing entered at all Enter at least one data row.
two Continuous segments overlap The X ranges in rows 5 and 6 overlap.
two Discrete rows share an X Rows 3 and 7 have the same X value.

Row numbers in the last two messages are grid row numbers, and they name the true neighbours whatever order you entered them in.

One problem is reported at a time, in the place it belongs: the across-rows checks wait until every individual row is clean.

Saving and removing

Save writes the distribution into the workbook and keeps it open for further editing. It is disabled while anything above is unresolved.

Remove Distribution deletes the loaded distribution from the workbook, after one confirmation:

Remove the custom distribution 'Supply' from this workbook?

It does nothing when you are building a new distribution that has never been saved.

Close leaves the dialog. Anything unsaved is discarded.

Using it

Once saved, the distribution appears by name at the end of the distribution list wherever that list is shown, after the built-in distributions.

Two kinds of distribution are defined in the workbook rather than chosen from the list, and both are added to the end of it.

A custom distribution is one you build yourself. It appears under the name you gave it.

An Empirical variable is a column of your own sample data. It appears as its name followed by (Empirical).

Neither has parameter boxes, because neither keeps its data in the marking: the data stays in the workbook and the model reads it from there. Selecting one and marking writes only its name.