Mann Kendall test for trend in time-series data help please!!

I am basically trying to use the Mann-Kendall test for a trend in time series data (Daily data sets going back to about 1980).

The only part I cannot calculate with normal excel formulas is the second half of the formula for VaR(S) from this website..

http://tenyearsnow.wordpress.com/201...rical-example/

Basically, i want to calculate this figure at each data point, looking back at the previous n-1 points. I guess i should try and explain the rationale of this part of the equation...

If we had n=1 to 10 as the following data points.. 9,8,8,6,5,4,4,3,2,1 (obviously there would also be lots of preceding data points).

g is a tied group when, in a data set, 2 or more of the data is the same. so here we have two tied groups as there are two 8's and two 4's. For each tied group we need to calculate tp(tp-1)(tp+5), (where tp is the number of data points in each group g, so here both groups would have tp=2 as there are two 8's and two 4's).

Then summing together all tp(tp-1)(tp+5) for all g's withing the 10 data points.

The hard part is that i want to do this at every point in the data. I also want to test this for varying lengths, for example n=1 to 20, n=1 to 30 etc.

The furthest i have ever really got with VBA is Alt-F11, so any help will be most appreciated.

Thank you, I have done as said, on sheet 1. The yellow column is where the help is needed,with some notes added. If any further info is required please say. Many Thanks again.

For Cell R5, the comment is meant to say "Looking at O5:O13", apologies.

I am a beginner at this and the following formula is about as far as i can get, which is obviously nowhere near.....





Sub Loop1()

Do

If ActiveCell.Offset(0, -2).Value = ActiveCell.Offset(i, -2).Value Then


c = 1

ActiveCell.Value = "c * (c-1) * ((2*c)+5)"

ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, 1))


End Sub

Thanks. What I really need help on is coding a conditional summation for, say, Cell R3, that can check the values in cells O3 to O12, against the range of O3:O12.

I just dont really know how to start. I mean, I have done programming in MatLab before, it is somewhat similar but i dont know a lot of the function names.

Kind of, from my data set i have calculated the S value for each point. (there are about 5000 data points) I now need to calculate Var(S) for each point but cannot find an obvious way to do it with normal Excel formulas.

If you see my original post, there is a link to the formula for Var(S) of which the second half I am trying to code in VBA. I have (tried) to give an explanation in the original post as to the logic behind the calculation.

Hi Richard, thats almost it, looking at z 13 for example, its taking three -20's as two tied groups, because its found -20 appears 3 times (cell AG13) and -20 appears twice (cell AH13). This should be one tied group of tp =3, and the figure in the z column should equal 3*(3-1)*([3*2]+5)=66. Hope this makes sense.

As another example, cell z19 (which should find tied groups of three -20's, two -19's and two -15's, should equal 3*(3-1)*([3*2]+5) + 2*(2-1)*([2*2]+5) + 3*(2-1)*([2*2]+5)

Hello,
I urgently need a sheet in excel format through which I can use the test mann kendall ... Thank you for the valuable help that you give me .... I do not know really how to do ...

I apologize, but I do not know English well, and I have not figured out how to open a new discussion although I have read the forum rules, although I have much urgency to solve my problem with the test mann kendall
Thanks for the forbearance