2011-01-12 03:41:31Three Questions
Robert Way

Hey all

Does anyone know how to detrend in excel?

Also John, I was wondering whether there is any way to see what graphics we have already uploaded?

Finally, how do people convert matrix into vectors. For example if I want to plot monthly data but I get it in this format
               Jan Feb Mar  ..etc...
2011        -10  12   14
2012        -9    11   15

How do I bring it to this format:

2011 -10


Robert Way
2011-01-12 05:58:00
Ari Jokimäki


For the third question, I have done it with two methods:

1) Manual - Import the file to Excel and insert enough rows between the first and the second year. Then enter at first empty row and at the value column (probably temperature?): "=B1", where the B1 is the column and the row of the february value. Enter "=C1" to the next row for March value, "=D1" to next, and so on. When you have done this for the first year, you can then copy paste the equations from the first year so that the values of the second year get copied already when you do the paste. Repeat for all years.

2) Automatic - I once made a conversion program (with C-language) that does this for HadCRUT3 format files. I have been thinking that I should do this for GISS files too and perhaps to some other files also and then distribute the programs by my blog for example. What is the specific dataset you are looking to convert?

2011-01-12 07:32:50Hey Ari
Robert Way

Hey Ari,
I'm not sure the manual methods works or perhaps I have misunderstood. Why would the formula copy across. You also mentioned there was a big space so wouldn't the reference be lost because of it?

Regarding the automatic ways, I am able to do it with Matlab very easily (Import Ascii grid, convert to vector with (:) and then Export Ascii grid) but I was looking for a method that was quicker and that I could just do in excel. I have a program in excel (a macro) which takes data which is vector and turns it into a grid but not the other way around. Regarding which dataset it is, it isn't terribly important at this point as to which specific one but it is just that it happens so frequently that I would like to be able to find a simple solution. Thanks for the help though!
2011-01-12 22:40:39
Ari Jokimäki

Robert, the manual method works. I have used it. It's just a bad explanation from me. It's a set of Excel formulas that copy the contents of the cells in side-by-side columns to cells in rows. It has to be done for one year and then it can be copy-pasted to other years. It's slow to do, but I think you could record a macro to do it. I haven't used macros much, so I don't know about that.