Finding Patterns in Numerical Data

[DrSpirograph]

Hi,
We're trying to find some trends in some data that we've got, we've got a series of numbers from 1-98 ordered (psuedo-randomly) by users and I've put that into columns.
I'd like to find any patterns in the data, ie if two users have put the same numbers in the same order - but not necessarily in the same position.
ie, if one user has the sequence 5,88, 13 at the start, and another user has 5, 88, 13 at the end, we'd like to discover that.

Another way to think of it is, if we were to chart the data in a line graph, I'd like to find any areas that either overlap, or could overlap if you shifted one of the users data along the horizontal plane.

I'm thinking this is too complicated to be solved by a function, but perhaps there's a macro out there that could do this?

I'm not afraid of writing a complicated formula if someone will point me at the right functions to look at.

Cheers,
Chris

[kingfisher]

COUNTIF is one function you could use.

[acknak]

Actually, the human eye/brain is probably about the best pattern finder. All you have to do is present the data in a way that graphs the relationship(s) you're interested in, then you can simply "look at it" to find patterns.

If you're interested in the sequence of the numbers, one of the simplest ways to find patterns is called a "dot plot". All you do is label two adjacent sides of the grid with the two sequences, and color each grid square if it's two numbers are the same. Wherever you have common sub-strings in the two sequences, they will form a diagonal in the plot, which is very easy to see. Sub-strings in the same direction will form along one diagonal; sub-strings in the opposite direction will form along the opposite diagonal. It doesn't matter where the matching sub-strings appear in each sequence.

It's trivial to set this up in a spreadsheet, although it may not perform very well with a 100x100 grid.

Here's a 15x15 sample:

dot_plot.png (3.96 KiB) Viewed 2512 times

[Dave]

Would you not have to define what sort of pattern you are looking for? You have given an example using three consecutive entries. Could it be only 2? Would you also look for more than 3? Do they have to be equal, or could one set be the same multiple of another? Series can be arithmetic, geometric, hyperbolic, .... What sort of "pattern" do you have in mind? Acknack is correct, basing that on the reason as to why we use graphs and functions in the first place. The graph, the visual representation, makes it easier to spot pattern. Relations can be simple or complex.

David.

[DrSpirograph]

Cheers guys,
Sorry for the late reply. Yes, I was after patterns of any length. I ended up graphing them and that was the best way to find them.

[Villeroy]

Dr. Spirograph,
Completely off topic: I just thought that someone with your nick name may be interested in Danny's Draw Power Tools


http://en.wikipedia.org/wiki/Spirograph