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

Please Login or Register  to view this content.

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

[MarkFranklin]

Aplogies for that - had my zip set to maximum compression - which isn't always the best idea.

This is back to normal now!

Thanks

Mark

[rylo]

Mark

I don't know if this is just a fluke or not but:

1) I imported the text into a spreadsheet. The Time SG and RF were in columns A:C. The data to test is in column J.
2) I put the function I gave earlier into the spreadsheet.
3) I then ran the code below

Please Login or Register  to view this content.

This puts all those items where the SG is >= .8 and the RF <> . into columns O:Q.

4)In S2: =mymatch(Q2:Q626,J2:J25)
This gives a result of 111 which is the starting row in column O that is a match.

As I said, I don't know if this is a fluke, or is a valid way of working your data. If the broad approach would work, but the function comes back with no match, then you could "fiddle" with the column J ranges and see if anything matches.

Does that help???

rylo

[MarkFranklin]

Yes, thanks Rylo, you are a star!

I am going to go away now and read a visual basic book - then try to learn how to put everything together.

Mark

[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