# Measuring Noise in Excel

I have 2 datasets:

One looks like:

``````1
1
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
3
3
3
3
3
3
3
3
3
3
3
3
``````

The other looks like:

``````1
3
3
1
1
3
1
1
1
1
1
1
2
2
2
2
2
2
3
3
3
3
3
3
2
3
2
1
1
3
1
1
1
1
1
1
2
2
2
2
2
2
3
3
3
3
3
3
1
1
3
2
``````

The idea is that the first set varies very little, it only really changes twice, so I would say it varies very little.

The second set varies quite a bit more than the first one. The both might contain the same number of 1's, 2's and 3's in the end, but the importance is order.

The problem with variance is it does not take order into account, so both sets might have the same variance. I'm trying to measure the noise if you will in the data set. For example, a random set of 1's 2's and 3's would be basically 100% noisy. A list of just 1's would be no noise at all.

It does not have to be perfect, I just want to objectively measure how noisy the data is in some way with Excel.

• This is really a statistics question rather than an excel problem. Cross Validated might be a better site. – fixer1234 Apr 28 '16 at 19:09

With no thought put into the actual stats of this I would do the following.

Formula entered as an array formula ctrl+shift+enter

`=SUM(ABS(A3:A5-A2:A4))`

It calculates abs(A2-A1)+abs(A3-A2)+abs(A4-A3)... and so on as long as your range is.

Could also use an average or whatever function you want but it should give you some idea of the noise.

``````     1    2     3
A    1
B    2
C    2
D    3
``````
• In cell B2, place `=IF(A2=A1,0,1)`
• In cell B3, place `=IF(A2=A3,0,1)`
• Pull them down

This will result in something like this:

``````     1    2     3
A    1
B    2    1     0
C    2    0     1
D    3    1     0
``````

Column 2 will count shifts, while column 3 will count the repeated values.

At the bottom, sum columns 2 and 3 and divide 3 by 2. This will result in a measure of variance (# of changes / # of repeats, in this case = 1/2 = 50% repeats and 50% changes)