Embedded Files
Linear Statistical Tolerance Analysis
Linear Statistical Tolerance Analysis
•Intuitive to use (the user can build required calculations using standard Excel functions)
•Build sub-calculation blocks which can be reused in multiple analyses
•Not suitable for any non-linear operations such as multiplication or trigonometric functions. Use Monte Carlo Simulation for these functions instead.
Monte Carlo - Tolerance Analysis
Monte Carlo - Tolerance Analysis
Brief
Create an efficient Monte Carlo Simulation which is easy to use.
Executive Summary
Tool uses Excel as an intuitive user interface to build tolerance calculations, run analysis and read the results.
Monte Carlo tolerance simulation performed in Python 3 with powerful data processing libraries behind the scenes.
Excel sheet can remain open whilst simulation running
The System
The System
Excel Interface:
Excel cells have titled references helping to build easily readable formulae
VBA Macros automate name assignment and run the Python simulation script all from within the Excel window
Behind the Scenes:
Python extracts raw data and returns processed data from Excel files using the win32com library
Python processes Excel formulae into Python executable formulae
Python uses the Pandas library to process the Monte Carlo simulation efficiently
Validation
Validation
The random normal data generated by the simulation was validated by calculating the standard deviation and mean and comparing it to the input parameters over 1,000,000 iterations.
Calculation validation was completed using a linear formula. The simple statistical calculations gave model answers for the mean and standard deviation. These compared favourably against the output of the discrete simulation.
Learning Opportunities
Learning Opportunities
Python 3 has lots of good support and tools for fast, efficient data analysis including within Excel
Pandas Python library excellent for manipulating large data sets
Win32com Python library can manipulate open Excel documents.
Simple VBA shell script can call Python functions to return data or action commands.
Using ‘Eval’ to interpret custom formulae concatenated as strings is significantly more efficient than selectively working cell by cell in a ‘for loop’.
Page updated
Google Sites
Report abuse