Finding Patterns in Numerical Data

Discuss the spreadsheet application

Finding Patterns in Numerical Data

Postby DrSpirograph » Sun Apr 13, 2008 1:59 am

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.

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

Re: Finding Patterns in Numerical Data

Postby kingfisher » Sun Apr 13, 2008 4:48 am

COUNTIF is one function you could use.
Apache OpenOffice 4.1.6 on PCLinuxOS
User avatar
Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am

Re: Finding Patterns in Numerical Data

Postby acknak » Sun Apr 13, 2008 5:29 am

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 1665 times
(9.93 KiB) Downloaded 100 times
AOO4/LO5 • Linux • Fedora 23
User avatar
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Finding Patterns in Numerical Data

Postby Dave » Sun Apr 13, 2008 6:02 am

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.

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

Re: Finding Patterns in Numerical Data

Postby DrSpirograph » Wed Jun 25, 2008 5:01 pm

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.
Posts: 3
Joined: Sun Apr 13, 2008 1:52 am

Re: Finding Patterns in Numerical Data

Postby Villeroy » Wed Jun 25, 2008 5:42 pm

Dr. Spirograph,
Completely off topic: I just thought that someone with your nick name may be interested in Danny's Draw Power Tools
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
User avatar
Posts: 28851
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Return to Calc

Who is online

Users browsing this forum: No registered users and 29 guests