Matching data series

[MarkFranklin]

Hi, I want to match rainfall with historical rainfall.
It is quite simple to match a single value but I want to mtach a series or closest series (or series')

Eg for the last 6 measurements I get an couple of columns like this:
Time value
1 5
2 4
3 5
4 2
5 5
6 5

and I have historic data like this
Time value
1 1
2 2
3 0
4 1
5 3
6 5
7 5
8 4
9 5
10 2
11 5
12 5
13 3
14 2

so the match value I want is 7 as this is the start of time series that matches my observed values. Can any one help?

Thanks

[rylo]

Hi

Easiest way I can think to do this is build your own function.

In a general module of the spreadsheet where the data exists, enter the code

Function MyMatch(rng1 As Range, rng2 As Range)
  Dim arr()
  cntr = 0
  For i = 1 To rng1.Rows.Count - rng2.Rows.Count + 1
    ReDim Preserve arr(cntr)
    holder = ""
    For j = 1 To rng2.Rows.Count
      holder = holder & rng1(i + j - 1) & ","
    Next j
    arr(cntr) = Left(holder, Len(holder) - 1)
    cntr = cntr + 1
  Next i
  
  For i = 1 To rng2.Rows.Count
    strr = strr & rng2(i) & ","
  Next i
  strr = Left(strr, Len(strr) - 1)
  
  On Error Resume Next
  matcher = WorksheetFunction.Match(strr, arr, 0)
  On Error GoTo 0
  
  If matcher = "" Then
    MyMatch = "Not found"
  Else
    MyMatch = matcher
  End If
End Function

Say your historical data is on sheet1!A1:B15 (including headings) and your test data is in sheet1!F1:G7 (including headings) then enter the formula

=mymatch(B2:B15,G2:G7)

See how that goes.

rylo

[MarkFranklin]

Ignore this first bit - it works now (I don't know why)
>I am sorry - I have tried pasting the function in and I get a #name? error - ie it doesn't recognise the function. I >have (hopefully attached a screendump showing what I ahve done)
>
>Can you explain how to get the function correctly registered please?
>
>Thanks a lot for your input.
>
>Mark

PS The end result I want is to be able to select time periods where there has been similar rainfall profiles to what I have observed recently. So my follow up question is going to be how do I get excel to match when similar profiles are recorded (say +/- 0.5 per point)...

[rylo]

Mark

I think you are heading towards a "fuzzy logic" approach to a solution.

I haven't looked at this in any detail so can't come up with anything quickly.

It may pay you to do a search on some other solutions, and see if they will give you some clues. Go to Google, and in the search criteria enter

fuzzy logic site:mrexcel.com

Have a look through the results it returns, and see if you can get something from there.


EDIT:
Can you post up some more realistic data to cover your request. Both Historical and Sample, and where a "match" would exist. Had a quick look at the fuzzy logic items and most seemed to apply to string data, not numeric.


rylo

[MarkFranklin]

Rylo,
Thanks a lot for putting me on to the Fuzzy logic approach. I can do quite a lot with your previous help but now it sounds like I need to give you the whole problem not parts of the problem!

Essentially I wish to find periods which had similar rainfall and river levels - the idea for the future is to develop tools for assisting with river level forecasting. If you have some rainfall data /forecast rainfall totals you can match periods when the amount of rainfall that has recently fallen in an area with some historical rainfall.

In the example (combo.zip) file I have:
column 1 timestamp and can be used in lookups etc to plot graphs
column 2 recorded river level at the gauge
column 3 recorded rain at the gauge
there is also a column of rain values I want to match. I want to get say five to ten matches based on the rainfall and perhaps say only when the river level was about the same as at the time...

Do you think it would be possible to do this/to modify your excellent existing routine to do this?

Thanks

Mark

[rylo]

Mark

Seems to be a problem with the zip file for the sample data. Can you have a look and repost.


rylo

[Nabeel]

Hi, This post is very informative, however I would like some specific information. If someone can help me then please send me a private message. Best Regards,

[rylo]

Nabeel

You would be better off creating a new post, outlining / detailing your requirements, and providing a link to this post for reference.

At least then we have some idea of what you are trying to achieve.


rylo