I have an n x m table , n (rows) are years, m (cols)are months, values are temperatures.
How can I create a vector just containing the values in the correct sequence?
E.G In
Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
1950 x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12
….
2021 y1 y2 y3 y4 y5 y6 y7 y8 y9 y10 y11 y12
Out
1950-01 n1
1959-02 n2
…
2021-12 m12
This should be easy no? But I can only find a complex, nested, messy function on youtube.
I wish to create a single vector as input to a graph with dates (yyyy-mm) on the x-axis and temperatures on the y-axis
Some climate sites show their data in the form I’ve described eg. https://origin.cpc.ncep.noaa.gov/products/analysis_monitoring/ensostuff/ONI_v5.php
Thank-you
—–
Pete
There's no easy way. But if you use the INDEX function you can construct another table that does it. Assume your first table is named "Temps" and column A is years, row 1 is months, but as numbers: 1-12.
So the first data row of the other table would be 2 and 2 in the first two columns (Cells A2 and B2). Then the two columns would be 2,3 then 2,4, down to 2,12 and then 3,2 and 3,3, etc.
This gets you those values like "1950-01" in column C.
=INDEX(Temps::A,A2)&"-"&RIGHT(100+INDEX(Temps::$1:$1,B2),2)
Seems complex, but a lot of it is just to deal with showing the month like 01 instead of 1. Otherwise, it would be:
=DATE(INDEX(Temps::A,A2),INDEX(Temps::$1:$1,B2),1)
This would create a real date value and you could format that column any way you like.
Then a simpler INDEX function gets you the temperature
=INDEX(Temps::A$1:M$99,A2,B2)
Then you can hide columns A and B if you like.
Another way to do it would be to use INDEX with the entire table in the first parameter, then MATCH for the row and MATCH for the column. So like this:
INDEX(Temps::A:M, MATCH(1950,Temps:A), MATCH(1,Temps:$1:$1))
But that can be more complex depending on what you are used to.
Thanks Gary, that works! Creating your other table is time consuming when the 'Temps' table is large.
I mentioned a messy solution on YouTube - https://www.youtube.com/watch?v=OG_yNrQNJ6g , he shows this formula
=INDEX(Temps,1+INT((ROW(A1)-1)/COLUMNS(Temps)),MOD(ROW(A1)-1+COLUMNS(Temps),COLUMNS(Temps))+1)
It works in EXCEL, bur errors in NUMBERS, can this be fixed? Thanks in advance
Pete: Lots wrong in that formula. Look up each function in Numbers and compare the examples. For instance, COLUMNS needs a range, and you are only giving it the name of a table. See how I use it in my example.