USING A SPREADSHEET TO FIND MAD

Spreadsheets can be used to find the mean absolute deviation of a data set.

Example :

A paper mill is testing two paper-cutting machines. Both are set to produce pieces of paper with a width of 8.5 inches. The actual widths of 8 pieces of paper cut by each machine are shown.

Widths of Pieces of Paper Cut by Machine A (in.) : 

8.502, 8.508, 8.499, 8.501, 8.492, 8.511, 8.505, 8.491

Widths of Pieces of Paper Cut by Machine B (in.) :

8.503, 8.501, 8.498, 8.499, 8.498, 8.504, 8.496, 8.502

Use a spreadsheet to determine which machine has less variability and, thus, does a better job.

Solution :

Step 1 : 

Enter the data values for Machine A into row 1 of a spreadsheet, using cells A to H.

Step 2 : 

Enter “mean = “ into cell A2 and the formula = AVERAGE(A1:H1) into cell B2.

Step 3 : 

Enter “MAD = “ into cell A3 and the formula = AVEDEV(A1:H1) into cell B3.

The MAD for Machine A is about 0.0054 in.

Step 4 : 

Repeat Steps 1–3 with the data values for Machine B.

The MAD for Machine B is about 0.0024 in.

Machine B has less variability, so it does a better job.

Finding MAD without spreadsheet

Problem :

The data represent the height, in feet, of various buildings. Find the mean absolute deviation.

60, 58, 54, 56, 63, 65, 62, 59, 56, 57

Solution :

Let x  =    60, 58, 54, 56, 63, 65, 62, 59, 56, 57

The mean is given by

x̄̄  =  (60+58+54+56+63+65+62+59+56+57) / 10

x̄  =   590 / 10

x̄̄  =   59

Absolute deviations of observations from mean : 

|60 - 59 |  =  |1|  =  1

|58 - 59 |  =  |-1|  =  1

|54 - 59 |  =  |-5|  =  5

|56 - 59 |  =  |-3|  =  3

|63 - 59 |  =  |4|  =  4

|65 - 59 |  =  |6|  =  6

|62 - 59 |  =  |3|  =  3

|59 - 59 |  =  |0|  =  0

|56 - 59 |  =  |-3|  =  3

|57 - 59 |  =  |-2|  =  2

Calculate the MAD by finding the mean of the above absolute deviations of observations from mean.  Round to the nearest whole number.

MAD  =  (1 + 1 + 5 + 3 + 4 + 6 + 3 + 0 + 3 + 2) / 10

MAD  =  28 / 10

MAD  =  2.8 ≈  3

Kindly mail your feedback to v4formath@gmail.com

We always appreciate your feedback.