CMIP5 Model Temperature Results in Excel

Guest Post by Willis Eschenbach

I’ve been looking at the surface temperature results from the 42 CMIP5 models used in the IPCC reports. It’s a bit of a game to download them from the outstanding KNMI site. To get around that, I’ve collated them into an Excel workbook so that everyone can investigate them. Here’s the kind of thing that you can do with them …

You can see why folks are saying that the models have been going off the rails …

So for your greater scientific pleasure, the model results are in an Excel workbook called “Willis’s Collation CMIP5 Models” (5.8 Mb file) The results are from models running the RCP45 scenario. There are five sheets in the workbook, all of which show the surface air temperature. They are Global, Northern Hemisphere, Southern Hemisphere, Land, and Ocean temperatures. They cover the period from 1861 to 2100, showing monthly results. Enjoy.

Best to all,

[UPDATE] The data in the spreadsheets is 108 individual runs from 42 models. Some models have only one run, while others are the average of two or more runs. I just downloaded the 42 individual runs data. The one-run-per-model data is here in a 1.2 Mb file called “CMIP5 Models Air Temp One Member.xlsx”. -w.

[UPDATE 2] I realized I hadn’t put up the absolute values of the HadCRUT4 data. It’s here, also as an Excel spreadsheet, for the globe, and the northern and southern hemispheres as well.

[UPDATE 3]

For your further amusement, I’ve put the RCP 4.5 forcing results into an Excel workbook here. The data is from IIASA, but they only give it for every 5-10 year span, so I’ve splined it to give annual forcing values.

Best wishes,

0 0 votes

Article Rating

240 Comments

Inline Feedbacks

View all comments

rgbatduke

December 23, 2014 6:32 pm

I ask in part because in your graph, log(CO2) is an absolutely smooth curve from start to finish, and I’m not buying that in the slightest. Even the post-1959 curve is incorrect, the MLO data is nowhere near that smooth.

Hmm, maybe we aren’t looking at the same Mauna Loa data? Anyway, here:
http://www.phy.duke.edu/~rgb/cCO2oft.jpg
Note that there are three data sources plotted. Laws (black x’s), Siple (blue circles), and Mauna Loa. (black circles). The blue curve you can’t see under the data points is my the function used to generate the cCO2 used in the fit, as well as the ~rcp8.5 extrapolation to 2100. The red curve is a smoother (and more optimistic) fit to the ML data (but still more pessimistic than rcp6.5).
ML data is actually awesomely smooth. Laws is less so — there are a couple of stretches where it barely goes down in there, but then, Laws claims absurd annual resolution (IMO). Siple is very coarse grained, but probably more believable because of that. It is really a broad approximation that is consequently probably accurate enough.
All three are good enough fits to my curves, IMO. Do you disagree?
rgb

Willis Eschenbach

Author

Reply to rgbatduke

December 23, 2014 6:49 pm

Thanks, Robert. No, I don’t agree, because if you click on the graph and look closely at the big version you can see that the blue line used in the original graph is quite different from the Laws data … and in addition, the ML data is not “awesomely smooth”. There’s a clear jog in the ML data around 1990 that is not shown by the blue line. Try plotting the actual data with a line instead of big clumsy Xs and Os and you’ll see what I mean.
So I’ll stand by my statement that whatever the blue line was, it wasn’t an observational record of CO2 variation. Is it a “good enough fit”? Depends on your purposes. But it was immediately apparent to me that it wasn’t actual observations, which is why I asked …
Best to you,
w.

Willis Eschenbach

Author

Reply to rgbatduke

December 23, 2014 7:07 pm

Oh, one further quick question, Robert. Why do you think that the Law Dome results have “absurd annual resolution”? The CDIAC says:

The ice cores were dated by counting the annual layers in oxygen isotope ratio (δ18O in H2O), ice electroconductivity measurements (ECM), and hydrogen peroxide (H2O2) concentrations. For these three parameters, each core displayed clear, well-preserved seasonal cycles allowing a dating accuracy of ±2 years at 1805 A.D. for the three cores and ±10 years at 1350 A.D. for DSS.

What’re the issues you see with that?
w.

Berényi Péter

Reply to Willis Eschenbach

December 24, 2014 3:09 pm

There is no issue with isotope ratios in ice itself. However, atmospheric gases, such as carbon dioxide, enclosed in bubbles within the ice is an entirely different matter. It takes lots of time for the ice to get compact enough to prevent communication between gases in it and the atmosphere, depending on rate of accumulation. Which is pretty low in Antarctica, so it can be as long as several millennia in the interior of this continent.
Therefore you can’t date gas inclusions by dating the ice around them.
You also have dust in the ice and microscopically thin supercooled water layers between ice crystals. So gases which readily dissolve in water (like CO2) keep reacting chemically with dust particles, long after they have got trapped. Ultramafic volcanic dust is especially good at absorbing CO2.

Willis Eschenbach

Author

December 24, 2014 12:22 am

More fun with the data … using the individual model dataset.

w.

Jan Kjetil Andersen

December 24, 2014 12:28 am

The IPCC averages the data over a full decade. This gives a better fit.
Here is also each model output averaged over one decade.
We see that three of the models give less warming than observed, but most give more.
/Jan

ferdberple

Reply to Jan Kjetil Andersen

December 24, 2014 6:44 am

Why stop there? Why limit the averaging to a decade? Why not average the models over a century? I expect the fit will improve dramatically as the length of the average increases.
The problem is that averaging makes you data look “better”, “more uniform”, and “more predictive” than it really is.
A straight trend-line is a form of averaging. In effect you have averaged your data out to infinity at each end, and then chopped it off to hide how silly the answer becomes towards the end points.
No matter what we do, as we straighten the results via averaging, the slightest trend leads to infinity at each end. runaway warming or cooling, as a result of mathematics, not CO2.

Jan Kjetil Andersen

Reply to ferdberple

December 25, 2014 6:42 am

Why stop there? Why limit the averaging to a decade? Why not average the models over a century?

You have a rhetoric point there Ferb, but it is only empty rhetorics. We all know that averaging over a decade makes sense in climatology; a century makes the forecasts less valuable because we will all be dead before we could see any trends.
Concerning the cutoff, I used data from 1970 to 2014 and with a 10-year moving average there has to be a 5-year cutoff in each end.
/Jan

johann wundersamer

December 24, 2014 5:03 am

models are models are models.
….
en.m.wikipedia.org/wiki/Laplace’s_demon
regards – Hans

johann wundersamer

December 24, 2014 5:16 am

models are models are …
ain’t no ‘super’computer able to represent the world.
at the best workarounds.
that’s what we can go for – Hans

johann wundersamer

December 24, 2014 5:24 am

so thanks, Willis Eschenbach,
for showing the tools! Hans

ferdberple

December 24, 2014 6:52 am

One thing I do see in the data is a reduction in variance going forward. In 1860 the range of results is about 4C. By 2040 this is about 3.5C.
So, what the models are showing us is that temperature is predicted to become less extreme, with less variability. The exact opposite of what scientists are telling us in the popular press.

ferdberple

Reply to ferdberple

December 24, 2014 7:39 am

nope, scratch that. the result was an artifact of averaging the models. remove the averaging and the trend disappears. once again demonstrating that averaging first is a mistake.

Ron C.

December 24, 2014 11:56 am

In presenting the CMIP5 dataset, Willis raised a question about which of the 42 models could be the best one. I put the issue this way: Does one of the CMIP5 models reproduce the temperature history convincingly enough that its projections should be taken seriously?
I have now had time to look at this and can comment based upon analysis of the temperature trends produced by each of the 42 models. To reiterate, the models generate estimates of monthly global mean temperatures in degrees Kelvin backwards to 1861 and forwards to 2101, a period of 240 years. This comprises 145 years of history to 2005, and 95 years of projections from 2006 onwards.
I identified the models that produced an historical trend nearly 0.5K/century over the 145 year period, and those whose trend from 1861 to 2014 was in the same range. Then I looked to see which of the subsets could match the UAH trend 1979 to 2014, and which showed the plateau in the last decade.
Out of these comparisons I am impressed most by the model producing Series 31.
It shows warming 0.52K/century from 1861 to 2014, with a plateau from 2006 to 2014, and 0.91K/century from 1979-2014. It projects 1.0K/century from 2006 to 2035 and 1.35K/century from now to 2101.

Berényi Péter

December 24, 2014 2:25 pm

CMIP5 model outputs are given as absolute temperatures (in K), which is good. Therefore hemispheric climatologies can be calculated, especially monthly differences between average temperatures of the two hemispheres. These functions should not be too sensitive to levels of well mixed atmospheric IR absorbers, because… they are well mixed.
Turns out series 1-42, as a set, is inconsistent according to this measure. It means they can’t possibly describe the same climate, they are too far apart for that. So, some models, included in this set, are provably wrong (possibly all of them).
Unfortunately in HadCRUT4 only anomalies are given, which makes it impossible to pick the worst (or best) model based on this particular set of observations.

Berényi Péter

Reply to Berényi Péter

December 29, 2014 11:11 am

Well, it is not completely true. The CRU Temperature page has a reference like “Absolute temperatures for the base period 1961-90 (see Jones et al., 1999)”.
It is this one.
Reviews of Geophysics, Volume 37, Issue 2, pages 173–199, May 1999
Article first published online: 14 JUN 2010
DOI: 10.1029/1999RG900002
Surface air temperature and its changes over the past 150 years
P. D. Jones, M. New, D. E. Parker, S. Martin, I. G. Rigor
On page 196 we find Figure 7 (Seasonal cycle of hemispheric and global mean temperatures in absolute degrees Celsius based on the 1961-1990 period).
If it is re-digitized, observed annual cycle (in K) is like this:

Mon  NH     SH      Global
01 281.11 289.53 285.32
02 281.66 289.26 285.46
03 283.74 288.35 286.05
04 287.00 287.18 287.09
05 290.25 285.91 288.08
06 292.79 284.73 288.76
07 294.15 283.92 289.03
08 294.05 283.83 288.94
09 292.25 284.28 288.26
10 288.99 285.55 287.27
11 285.19 287.18 286.18
12 282.29 288.72 285.50

It can be added to anomalies given in HadCRUT4 NH and HadCRUT4 SH respectively (links to metadata are at the HadCRUT Download page).
If it is done, we get something almost directly comparable to CMIP5 model outputs, except HadCRUT is sampled at mid-month while the 42 CMIP5 series given by Willis are sampled at the beginning of each month.
No matter, it can be re-sampled (using cubic interpolation for annual cycles and linear interpolation for anomalies). You get something like this.
If it is compared to CMIP5 output series, global averages do match observations reasonably well for most model outputs from 1861 to Nov 2014 (the last data point in HadCRUT).
Average error is less than 1K for all models and the best one (S1) has only 0.27K.
Unfortunately this may well be an artifact, partly because models are tuned to match past observations, partly because observations are adjusted to match models.
However, there are limits to tuning &. adjustment. If we check how well model runs reproduce monthly temperature difference between the two hemispheres, the most elementary regional skill imaginable and also pretty independent of carbon dioxide forcing, because it is a well mixed gas, model performance turns out to be awful.
Average error is smallest for S33, but it is still as large as 0.87K, comparable to all the warming observed in the last 150 years and larger than errors stated for HadCRUT 4.3.3, while for S34, which is the worst one in this respect, it is 2.1K. Average of this error term over CMIP5 time series is 1.17K.
Therefore all computational models included in CMIP5 are falsified.

David R

December 25, 2014 1:20 pm

Willis,
Are you sure there aren’t some CMIP5 models missing from the KNMI range?
From what I can make out using the data you provided, observations in 2014 will be below all the model forecasts; yet I have seen several charts showing CMIP5 models that are currently running cooler than observations for 2014. For instance, see Ed Hawkins’ chart here: http://www.met.reading.ac.uk/~ed/bloguploads/FIG_11-25_UPDATE.png
According to the CMIP5 site there are 61 models in the range, though that may have been reduced; I don’t know. Perhaps KNMI only has data for 42 of them.
Thanks for the work in getting what you did anyway. Merry Christmas (not too late to say that, is it?)

Willis Eschenbach

Author

Reply to David R

December 25, 2014 2:29 pm

First, thanks for the good wishes. On my planet, it’s never too late to wish someone well, at Xmas or any other time.
Second, I don’t know what the CMIP5 folks have, because their website is such a nightmare to navigate.
Finally, regarding whether the CMIP5 models are running hotter or cooler, remember that there are a half-dozen or so “RCPs”, the specifications for the concentrations of the various elements fed into the models. So it might be from that.
w.

David R

Reply to Willis Eschenbach

December 25, 2014 3:47 pm

It seems this whole CMIP5 business is fraught with difficulties. Hard enough to get the data; but even then, it seems, there are so many permutations that nearly any claim re whether observations are hotter or colder or spot on, can be substantiated or refuted!
I enjoyed looking over the data you posted though. Thanks again for that.

Terry Oldberg

December 25, 2014 9:58 pm

While one can compare the global surface temperatures results from the CMIP5 models to the HadCRUT4 global surface temperature time series this comparison is not logically or scientifically meaningful. The logically and scientifically meaningful comparison would be between the predicted and observed relative frequencies of the outcomes of the events underlying the model but such a comparison is not possible as there are no such events!

December 26, 2014 5:30 am

Willis,
I would like to add my thanks to you for posting these files.
I have attempted to obtain the data via the CMIP5 and KNMI websites, so far without success.
However, are you sure that the links to the files are correct?
They both seem to point to the same files (the multiple run one) to me and I can’t download the “one run per model” file.

Willis Eschenbach

Author

Reply to QV

December 26, 2014 10:38 am

YIKES! You’re right. I’ve fixed the link, thanks for pointing it out.
Best regards,
w.

quaesoveritas

Reply to Willis Eschenbach

December 26, 2014 10:48 am

Phew!
I thought I was doing something wrong.

Ron C.

December 26, 2014 10:03 am

Further to my comment about Series 31 above, I have looked more into the details, and I am less impressed, though it is probably one of the best in the CMIP set. The historical part of the series does not present any plateau, either last century or this one. Moreover, as is typical of all these models, the future is projected to warm at a rate 3 times that in the history up to 2005.

Willis Eschenbach

Author

December 26, 2014 10:36 am

I realized I hadn’t put up the absolute values of the HadCRUT4 data. They’re here, also as an Excel spreadsheet, for the globe, and the northern and southern hemispheres separately.
Regards to all,
w.

Lance Wallace

December 26, 2014 11:57 am

For what it’s worth (I think very little), the 10 “best” models according to the highest Spearman correlations vs. HADCRUT4 for the 1847 months from 1861 to November 2014 are as follows:
SERIES SPEARMAN RANK-ORDER COEFFICIENT R
Series 5 0.37
Series 23 0.31
Series 7 0.31
Series 22 0.30
Series 8 0.30
Series 3 0.30
Series 14 0.30
Series 32 0.29
Series 36 0.29
Series 20 0.28
Choosing the median or mean of the CMIP 42 produced a middling Spearman of 0.26. This is an argument against the idea that somehow the mean of the models will perform better than any single model.
By this measure, the 10 “worst” models were
Series 35 0.21
Series 17 0.21
Series 18 0.21
Series 13 0.21
Series 2 0.20
Series 10 0.20
Series 30 0.19
Series 40 0.19
Series 11 0.16
Series 29 0.15
Series 31, by the way, which received some attention above, was low on the list with a Spearman r of 0.22.

Brandon Gates

Reply to Lance Wallace

December 26, 2014 3:26 pm

Lance Wallace,
I chose my 10 best and worst vs HADCRUT4 over the reference period 1986-2005 and plotted those means against the entire ensemble:
https://drive.google.com/file/d/0B1C2T0pQeiaSa2JfcFF3UVItWTg
Bottom plot is the same analysis for the 10 best individual model runs (members) with similar results, though the 10 worst members are clearly somewhat “worse” than the 10 worst models. In both plots the ensemble mean is closer to the “best” curves. So perhaps this is an argument for “lose the 10 worst” or “keep only the 10 best”. I leave it to the reader to decide.

Lance Wallace

Reply to Lance Wallace

December 26, 2014 5:22 pm

Whoops, I made an error in these calculations. Should have done this separately for each month. That gives a very different set of “best” vs “worst” models. I’m not sure I have the correct HADCRUT 4.3 data so will not mention the present standings.

Brandon Gates

Reply to Lance Wallace

December 26, 2014 8:30 pm

It happens. Months are rather noisy, so I’m doing mine against annual means. I’m also doing it against anomalies, not absolute …. been too lazy to do both and compare.

Lance Wallace

Reply to Lance Wallace

December 27, 2014 3:43 pm

OK, I’ve now carried out the Spearman correlations by year and think I may have it right. Here are the top 10 models (out of 42 CMIP5 models and the mean and median).
SERIES SPEARMAN
MODEL MEAN 0.82
Series 26 0.81
Series 22 0.81
Series 7 0.80
Series 32 0.80
Series 20 0.80
Series 14 0.79
MODEL MEDIAN 0.79
Series 21 0.79
Series 39 0.79
and the bottom 10
SERIES SPEARMAN
Series 12 0.63
Series 19 0.63
Series 40 0.63
Series 10 0.63
Series 2 0.62
Series 1 0.62
Series 18 0.56
Series 17 0.53
Series 29 0.43
Series 11 0.37

Lance Wallace

December 26, 2014 2:43 pm

Looking more closely at the Spearman correlations with HADCRUT 4 averaged across all models by month a curious fact emerges: the correlation coefficients are similar for three seasons but fall off considerably for the winter season (DJF). Has anyone noted that?
https://dl.dropboxusercontent.com/u/75831381/CMIP42%20SPEARMAN%20CORRELATIONS%20BY%20MONTH.pdf

Willis Eschenbach

Author

December 26, 2014 11:38 pm

Steven Mosher December 22, 2014 at 9:38 pm

Great work Willis.
Now find the best model. enjoy

Steven Mosher December 23, 2014 at 12:28 am

Logically this [that the model is more correct than the data] is a possibility that can’t be eliminated. every real skeptic understands this

Let me see if I can bring a little light to the discussion. I’m not Steven, obviously, so he may disagree.
First, when Mosh said “find the best model”, he was pointing out (in his usual cryptic fashion) that there is no “best” model in general. First, you need to decide what the purpose of your model might be. Do you want it to be best at hindcasting the past temperatures? Forecasting the future temperatures? Being right in the short term? Being right in the long term? Being right regarding precipitation? Having the smallest extreme error? Having the smallest average error? First you have to decide what you want the model to DO. Only then can you begin to determine which model is the best for that particular job.
Next, Mosh is right that a model may give you better answers than the data. As an example, consider the condition right after Newton’s Laws of Motion were first applied to the locations of the planets. At that time astronomical measurements were nowhere near as accurate as they are today. So it would have been perfectly possible to use the model (Newton’s Laws) to look at a series of observations and see which ones on them were least accurate, based on how well they fit the curvilinear motion predicted by the model. So the model in that case could indeed be more correct than the data, and likely was.
Of course, some hundreds of years later it was the difference between observations of the transit of Mercury and the model (Newton’s Laws) which showed that the model was incorrect for relativistic situations … and in that situation the observations were more correct than the model.
However, contrary to Mosh’s claim, as Robert Brown (rgbatduke) pointed out, the models being more correct than the data is a possibility that CAN be eliminated. It also requires a model which is better than our observational skills. Given how poorly the climate models reproduce the past and present, and given the quality of our modern measurements, in the climate arena this seems very unlikely.
For example, we currently have a leveling off of the temperature, which is not shown by any of the 42 models above. Now, it is a possibility that all of the models are correct and the observations are wrong … but in this case I’d say that it is a possibility that can be eliminated.
Finally, the failure of ANY of the models to forecast the leveling off of the temperatures over the last couple decades should disabuse people of the idea that the mean of an “ensemble” of models gives a better result than an individual model.
It should also disabuse people of making the claim that an agreement between all of the models means anything. The models had better than the famous “97% consensus” claimed for AGW scientists, and in the event both the models and the scientists were wrong about the leveling off of the temperatures.
My best to each and all of you,
w.

Ron C.

December 27, 2014 8:22 am

Thanks Willis for the HADCRUT4 dataset. It is my first time to look at the trends there. Interestingly, the series change points appear when you calculate the first differences and then scan the decadal averages of the differences.
On that basis, I got the following Global warming and cooling periods, with the linear trends for each:
Periods Decadal Rates
1850-1878 +0.035
1879-1888 – 0.215
1889-1900 +0.099
1901-1910 – 0.177
1911-1921 +0.224
1922-1929 +0.042
1930-1939 +0.139
1940-1954 – 0.055
1955-1976 – 0.040
1976-1986 – 0.004
1987-2002 +0.203
2003-2014 +0.120
Overall
1850-2014 +0.049
A few observations about making comparisons between CMIP5 models and HADCRUT4:
1. None of the models can produce warming and cooling periods at decadal levels. The parameters appear to be at century levels.
2. The significant rise in temperature from 1911 through 1939 does not appear in the models.
3. The significant decline in temperatures from 1940 through 1976 does not show up in the models.
4. HADCRUT4 does not show a plateau since 2002, only a reduced rate of warming.
Conclusions:
CMIP5 models are not able to reproduce HADCRUT4 variability.
The last decade of HADCRUT4 is suspect.

Brandon Gates

Reply to Ron C.

December 27, 2014 10:09 pm

Ron C.

CMIP5 models are not able to reproduce HADCRUT4 variability.

They don’t attempt to project the precise timing of things like ENSO, AMO, NAO and PDO. The planning horizon for CMIP5 is 50-100 years, not 10-30. Models aren’t gonna do what they haven’t been asked to do.

Ron C.

December 27, 2014 9:17 am

Addendum:
I noticed that excluding 2014 (since it is incomplete) gives a more reasonable rate for HADCRUT4 last decade:
2003-2013 – 0.022

Willis Eschenbach

Author

December 27, 2014 1:25 pm

Ron C.

December 28, 2014 9:06 am

Brandon
The estimated global mean temperatures are considered to be an emergent property generated by the model. Thus it is of interest to compare them to measured surface temperatures. The models produce variability year over year, and on decadal and centennial scales. So let’s compare CMIP5 Series 31 and HADCRUT4.
Periods Decadal Rate Rate Differences
HADCRUT4 SERIES 31 31 MINUS HADCRUT4
1850-1878 +0.035 +0.064 +0.029
1879-1888 -0.215 -0.004 +0.211
1889-1900 +0.099 +0.065 -0.034
1901-1910 -0.177 +0.090 +0.267
1911-1921 +0.224 +0.087 -0.137
1922-1929 +0.042 +0.212 +0.170
1930-1939 +0.139 +0.211 +0.072
1940-1954 -0.055 -0.016 +0.039
1955-1976 -0.040 +0.072 +0.112
1977-1986 -0.004 +0.196 +0.200
1987-2002 +0.203 +0.134 -0.069
2003-2013 -0.022 +0.012 +0.034
Overall
1850-2014 +0.049 +0.052 +0.003
2015-2076 +0.154
This analysis shows that Series 31 can be compared to HADCRUT4. While the overall historical rates are close, the model runs hotter than Hadcrut in nine of twelve periods.
The model shows warming in the 1920s and 1930s at a hotter rate. It shows 1940 to 1976 as a slightly warming, rather than cooling period. The warming since 1977 is comparable though it comes earlier in Series 31. The last decade is slightly warming rather than cooling. Finally, the model projects significant warming over the next 60 years.

Ron C.

December 28, 2014 1:17 pm

You can see why the correlation is positive, but poor
http://s6.postimage.org/wd131ao25/Decadal_Rates_Chart1_1.png

quaesoveritas

December 29, 2014 4:58 am

Willis,
Another stupid question.
Can you tell me what convention is used for months in dates?
e.g. does 1861.08333 represent January or February?
It seems logical (to me) that it should be January, but that would make 1861.000 December 1860.
I need this info in order to calculate annual averages correctly.

RACookPE1978

Editor

Reply to quaesoveritas

December 29, 2014 5:13 am

quaesoveritas (asking willis a question)
Another stupid question.
Can you tell me what convention is used for months in dates?
e.g. does 1861.08333 represent January or February?
It seems logical (to me) that it should be January, but that would make 1861.000 December 1860.
I need this info in order to calculate annual averages correctly.

Not a foolish question at all: Look at it from the simpler day-of-year (Julian date) aspect: January 1 is usually thought of as 001, but what happened to day 0? Also: S we progress through the 4-year Leap Year fisasco, what happens to solar radiation, for example, that does change slowly on a day-to-day basis, but the “day” is 267 3 years, then is 268, then is 267 again.

quaesoveritas

Reply to RACookPE1978

December 29, 2014 5:51 am

Thanks for your help, but I am afraid I don’t know how that answers my question.

Ron C.

Reply to quaesoveritas

December 29, 2014 5:58 am

I think if you use the INT function, you will get the years correctly. It simply removes the decimals, resulting in the number of the year.

Reply to Ron C.

December 29, 2014 6:08 am

Surely that way you get 12 identical “1861’s” and so on.
It doesn’t tell me whether to use 1861.000 or 1861.08333 for January.
If you are saying that 1861.000 is January, it seems slightly illogical to me because 1861.08333 is the end of the month. I might not be explaining this very well.

Ron C.

Reply to Ron C.

December 29, 2014 6:26 am

The series begins with the 12 months of 1850 and ends with nine months of 2014, so yes, 1850 is January, and 1850.916667 is December.

Ron C.

Reply to Ron C.

December 29, 2014 6:32 am

If you want to get annual averages, the INT function will give you 12 rows for each year, and a pivot table will produce the annual averages.

Reply to Ron C.

December 30, 2014 2:04 am

Thanks for the additional clarification.
When you say “The series begins with the 12 months of 1850 and ends with nine months of 2014”, I take it you are referring to HadCRUT4, not CMIP5? My CMIP5 spreadsheet starts with 1861.
I am afraid I never mastered pivot tables!

Willis Eschenbach

Author

Reply to quaesoveritas

December 29, 2014 8:51 am

quaesoveritas, on my planet the only stupid question is the one you don’t ask …
There are two different conventions in common use for representing dates. One uses e.g. 1990 to represent January, and the other uses 1990 + 0.5/12 (approximately January 15th) to represent January. I prefer the latter, but the computer language R uses the former. I used R to prepare the data, so 1900 is January 1900, and 1990.5 is July 1990.
w.

Reply to Willis Eschenbach

December 30, 2014 2:05 am

Thanks,
An additional complication!
I don’t use R, only Excel!

Karl-Heinz Dehner

December 29, 2014 12:32 pm

Hi,
I would be glad if I could investigate the provided CMIP5 model output. Unfortunately I haven’t the ability to process XLXS-spreadsheets. Is it possible for you to provide the data as XLS- or CSV-file too?
Many thanks!

Willis Eschenbach

Author

Reply to Karl-Heinz Dehner

December 29, 2014 3:20 pm

I can do better than that. Here’s how to open the new file format using your old Excel.
Macintosh
Windoze
All the best,
w.

Reply to Willis Eschenbach

December 30, 2014 1:46 am

I don’t know about anyone else, but can’t get the Windoze link to open.
The Macintosh link opens fine.

Karl-Heinz Dehner

Reply to Willis Eschenbach

December 30, 2014 10:46 am

After consideration I wouldn´t advise to do that, because ìt might be a spam provider.

Karl-Heinz Dehner

Reply to Willis Eschenbach

December 30, 2014 10:48 am

I give up, always hitting the wrong position …
Zamzar might be a spam provoder, so I don’t recommend it.

Ron C.

December 30, 2014 5:20 am

QV
You should treat yourself–pivot tables are one of the magical things where Excel does all the work for you. Here’s a good tutorial:
http://www.excel-easy.com/data-analysis/pivot-tables.html
This case is a good simple opportunity, since you have only 2 fields. Create a column called Year with the integers next to the column called Globe. Select the 2 columns and ask for a pivot table. Drag and drop the Year into the rows area and Globe into the data area and change the field setting to Average. That’s all there is to it.

« Previous 1 2 3 4 Next »

wpDiscuz

Welcome to Watts Up With That, one of the most well-known climate blogs! We gather the latest scientific research, news, and expert opinion to help you understand how our planet is changing and what implications it may have for humanity. Our approach is based on facts, objective analysis, and open discussions about one of the most critical issues of our time. Watts up with that climate and what changes await us – let’s figure it out together!

Watts Up With That covers a wide range of topics related to climate change and its impact on the world. Here’s what’s important to us:

Global warming – its causes, consequences, and future forecasts.
Analysis of current climate research and its findings.
Climate change news.
Extreme weather events – hurricanes, droughts, floods, and their connection to climate change.
The impact of different energy sources on the environment and the development of sustainable technologies.
Political and economic aspects and how states and international organizations respond to climate change.

Watts Up With That?

CMIP5 Model Temperature Results in Excel

Like this:

Share this:

Like this:

Related Posts

Claim: Climate Change is Killing all the Amazon Dung Beetles

Claim: Climate change may produce “fast-food” phytoplankton

Researcher Finds Proof The Met Office is Inflating UK Maximum Temperature Records

Aussie Select Committee on Information Integrity on Climate Change and Energy Delivers their Final Report