0

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.

  • 1
    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
2

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.

| improve this answer | |
1
     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)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.