Excel Formula for Custom Distribution

Objective

To create a distribution of values that meets the following criteria:

• 10% of values are above 155%
• 10% of values are below 75%
• The mean of the distribution is 100%

Approach

1. Normal Distribution Concept: To achieve the desired mean and specified percentiles, we will utilize a normal distribution with a mean (μ) and standard deviation (σ) that will fit the requirements.

2. Percentile Calculation: We will calculate the standard deviation needed to ensure that 10% of the values fall below 75% and 10% fall above 155%.

Formula Derivation

Z-scores Calculation

1. Identify the Z-scores corresponding to the 10th and 90th percentiles from the standard normal distribution.
   • For the 10th percentile (Z_10): approximately -1.2816
   • For the 90th percentile (Z_90): approximately 1.2816

Mean and Standard Deviation

Given:

• Mean (μ) = 100%
• Value at 10th percentile (P10) = 75%
• Value at 90th percentile (P90) = 155%

Use the Z-score formula:

• Z = (X - μ) / σ

Rearranging gives:

• σ = (X - μ) / Z

2. Calculating Standard Deviation (σ)

For the 10th percentile: σ = (75 - 100) / (-1.2816) ≈ 19.5

For the 90th percentile: σ = (155 - 100) / (1.2816) ≈ 42.9

Average Standard Deviation

Determine an appropriate standard deviation: σ ≈ (19.5 + 42.9) / 2 ≈ 31.2

Final Formula

The Excel formula for generating random values based on the above distribution is as follows:

Formula

= NORM.INV(RAND(), 100, 31.2)

Explanation

• NORM.INV: This function returns the inverse of the normal distribution for the specified mean and standard deviation, providing a value corresponding to a specified probability.
• RAND(): Generates a random number between 0 and 1, acting as the probability input for the NORM.INV function.
• 100: This is the mean of the distribution.
• 31.2: This is the derived approximate standard deviation that meets the criteria of the requested distribution.

Practical Example

1. Place the formula = NORM.INV(RAND(), 100, 31.2) in cell A1.
2. Drag down the fill handle to generate multiple rows of data (for example, A1:A100).
3. Use Excel tools such as COUNTIF or PERCENTILE to validate that approximately 10% of your generated values fall below 75% and 10% exceed 155%.

Conclusion

The provided Excel formula allows users to generate a distribution with specified constraints. This approach can be effectively implemented for simulations, modeling, and data analysis tasks where particular statistical characteristics are required. For further mastery of Excel data analysis techniques, consider exploring resources available on the Enterprise DNA Platform.