Excel Formula for Custom Normal Distribution

Objective

Create an Excel formula that establishes a distribution with the following characteristics:

• 10% of the values above 155%
• 10% of the values below 75%

Conceptual Approach

To achieve this, we can use the NORM.DIST, NORM.INV, and RAND functions. The focus will be on creating a normally distributed dataset where we manipulate the mean and standard deviation to ensure the desired thresholds.

Formula Breakdown

1. Establish Boundaries:

   • Lower Boundary: 75%
   • Upper Boundary: 155%

2. Calculate Mean and Standard Deviation:

   • A reasonable assumption for the mean can be the median of the two boundaries:
     • Mean = (75% + 155%) / 2 = 115%
   • Standard deviation can be estimated to spread the values appropriately.

3. Use of the NORM.INV Function:

   • This function returns the inverse of the normal distribution for a specified mean and standard deviation. By using a uniform random variable between 0 and 1, we can generate values across the distribution.

Excel Formula

To generate a random value based on the defined distribution:

=NORM.INV(RAND(), 115%, standard_deviation)

Explanation of the Formula

• RAND(): Generates a random decimal number between 0 and 1.
• NORM.INV(probability, mean, standard_dev):
  • probability: Random value from RAND().
  • mean: Set at 115% (the midpoint of the boundaries).
  • standard_dev: Standard deviation that should be defined to achieve around 80% of data in range between 75% and 155%.

Choosing Standard Deviation

To calculate an appropriate standard deviation, the goal is to ensure that approximately 10% of values fall below 75% and 10% above 155%.

Use the following approach to estimate standard deviation:

1. To find the z-scores corresponding to the cumulative probabilities:

   • For the lower threshold (10% percentile): z ≈ -1.28
   • For the upper threshold (90% percentile): z ≈ 1.28

2. Calculate standard deviation using:

   • You can rearrange the formula: ( \text{Standard Deviation} = \frac{(Upper Boundary - Mean)}{z} ) Substitute for Upper Boundary and Mean:
   • ( \text{Standard Deviation} = \frac{(155 - 115)}{1.28} )
   • Calculate for lower boundary similarly, confirming consistency.

Example

Assuming we find the standard deviation to be approximately 31.25%:

=NORM.INV(RAND(), 115%, 31.25%)

This formula will yield a random value based on the specified normal distribution, adhering to the constraints you've set forth.

Implementation Notes

• Drag down the formula in an Excel column to generate multiple values.
• Adjust the standard deviation as needed to fine-tune the distribution according to actual data characteristics.

Conclusion

By utilizing the NORM.INV function coupled with the RAND() function, one can effectively create a controlled distribution that meets specific lower and upper thresholds in Excel. For additional insights and advanced techniques, consider exploring the resources available on the Enterprise DNA Platform.