Find pattern in different sets of similar data and compare/find the closest ones.

I have got a data with date & time with two corresponding columns that contain certain values. I want to find dates that have similar 'patterns' of data. I ultimately want to predict what the next value is going to be of the current day data till a specific time, by comparing it with other days and finding the closest match.

and some manually calculated results

in the file will be nice.

Originally Posted by BMV

in the file will be nice.

actually i'm wanting to find ways/formulas to compare which data sets are similar. For example from 9:15 to 10:30 which data set has ascended or descended in a similar way so as i can make a prediction of what will happen next.

Originally Posted by WideBoyDixon

So I implemented a macro because I thought it was too "hard" for a formula. This is what I came up with:

Please Login or Register  to view this content.

It finds the closest match on a different day for each row. It takes a little while to run but might help you to identify the patterns that you're looking for.

WBD

it's finding the exact values from other days. i want to find the similar order of ascending or descending values. For example 4/26/19, 9:16 has 2 and 5. The next values for 9:21 are 5 and 11. I want to find which days had the similar ascend from 9:15 to 9:25.

Originally Posted by WideBoyDixon

Oh I see. I don't think the data lends itself to such scrutiny very easily. The measurements taken on different days differ in number (e.g. 69 in first day, 65 in second) so you can't match like that. The times also don't match so you can't use that as a comparison. The best I can think of is to calculate the "differential" between values and then run something like the above to determine when the same differential was observed. Tricky.

WBD

Instead of time we can replace it with an ascending series (1,2,3,4...). What is more important for me is to find a way to compare them. So for example if we want to compare the serial no. 10 of a day and find it's closest match, then the comparison has to take into account it's progression from serial no. 1 of the day. I want to find how to create the pattern (stdev, var, skew, kurt etc) through which we will compare.