What is I-MR Chart? How to create in MS Excel?

With an updated Excel template

What is a control chart?

We have already seen what a control chart is in an earlier short article. It helps the process owner to predict when his process is going to produce a defect.

Control charts are made by drawing mean line and control lines superimposing the simple line chart of process data*. Here depending on the type of data we possess the type of control chart, and the process data will vary.

What is I-MR Chart?

The MR Chart consists of two plots: the Individual Value plot (I) and the Moving Range plot (MR). In this article, we will see how to construct I and MR charts and discuss the need for a moving range plot in the following article on the Xbar-R Chart.

Sampling Method for I-MR Chart

A simple systematic sampling method calls for I-MR Chart. In this sampling method, we take one piece of sample at a regular interval of time.

Construction of I-MR Chart

I Chart

Formula for calculating Control Limit

Central Line = Mean of all individual values of the data set

Upper Control Limit = Mean + E2 * R-bar

Lower Control Limit = Mean – E2 * R-bar

E2

E2 is a constant derived according to the control chart constant table, which is available on the internet.

R-bar

*Note : I chart & MR chart is possible only when we follow Simple Systematic Sampling. In this method, every time we take only one sample. Meaning, the subgroup size is 1. For Subgroup size of 1, statistics does not recommend E2 value. Hence, we consider it as same as of subgroup size 2. Because, we are considering the next data point to calculate the moving range, we can consider a subgroup size of 2.

R-bar is the average of moving range.

Moving Range

What is moving range?

Moving range is the difference between two consecutive points of a data series. Consider a data series

Data PointsMoving Range
20
282
313
292
290
Average of Moving Range, R-bar= 7/4 = 1.75

We note the absolute value of the difference between the previous point and the current point as the Moving Range in that row.

For the moving range chart, the central line will be the Average of the Moving Range. We calculate the Upper and Lower control limits with a similar formula to the I chart.

Central Line = Mean of moving range

Upper Control Limit = Mean of Moving Range * D4

Lower Control Limit = Mean of Moving Range * D3

Control Chart Constants

How can we create I-MR chart in Excel?

I am still not sure why Microsoft is not providing control charts yet in Excel Data Analysis. There is no direct support to draw or create control charts in excel. So, we need to calculate the Average, Upper and Lower control limits for Individual Chart and similarly for Moving Range Chart.

I-MR Chart Excel template

Attaching an Excel template to reduce your workload. The formulas are visible for easy learning. Just copy-paste your data into the specified column and select the data range for the graph. You’ll get an I Chart and MR Chart.

I-MR Chart Excel template