## Finding Patterns in Numerical Data

### Finding Patterns in Numerical Data

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
DrSpirograph

Posts: 3
Joined: Sun Apr 13, 2008 1:52 am

### Re: Finding Patterns in Numerical Data

COUNTIF is one function you could use.
Apache OpenOffice 4.1.6 on PCLinuxOS

kingfisher
Volunteer

Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am

### Re: Finding Patterns in Numerical Data

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 1666 times
Attachments
dot_plot.ods
AOO4/LO5 • Linux • Fedora 23

acknak
Moderator

Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

### Re: Finding Patterns in Numerical Data

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.
Dave

Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

### Re: Finding Patterns in Numerical Data

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.
DrSpirograph

Posts: 3
Joined: Sun Apr 13, 2008 1:52 am

### Re: Finding Patterns in Numerical Data

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28851
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany