Excel - Spreadsheeting sea ice basics.

[ChrisReynolds]

Excel - Handling Sea Ice Data.

The source Excel workbook for this is available on Google Docs here. It's best not to view it from Google Docs, select File>Download and save or open direct.

There are 6 sheets in the spreadsheet.

• RawData - the point of entry for new data.
• Area - Cryosphere Today area tabulated data.
• Volume - PIOMAS daily volume tabulated data.
• CalcThick - Calculated thickness tabulated data.
• ThickSeries - Calculated thickness time series.
• Monthly - Monthly average values of various indices of sea ice.

In this first post I'll explain the nuts and bolts of how this spreadsheet is constructed. In this series my posts will be headed with a bold title so that they stand out from any discussion.

The first place to start is on the sheet RawData, click on the bottom tab and it will open. Note that between lines 10 and 11 there is a break, scrolling below that line moves the data up and down but keeps the headers of the columns visible, they don't disappear off the top of the screen. This is done using Freeze Panes (View > FreezePanes).

You'll see that there are five columns:

• Area.
• DataTable.
• Volume.
• Extent.

The URLs from which to obtain the source data are given above each column, apart from DataTable, which will be explained. I could use data links to update these source data columns (Data > Get External Data), however I've found them to be awkward to keep working through regular updates, so my update method is to do it manually. I'll be keeping that Spreadsheet up to date when PIOMAS data comes out each month. The way to update is described in a following comment, for now I want to keep on track with how all this works.

The key to the RawData sheet, and to subsequent sheet interactions with RawData is the DataTable table (columns G to L).

The table itself is in blue and the key column is the DateNum column. This obtains the date number index from the Area columns (Columns B to E), specifically from the Date column (column B) which is from the original Cryosphere Today area data index as downloaded from the stated webpage. The Year (G) is worked out as =INT(Bxx), this cuts off the CT date index to leave only the integer part. The day number (day of the year) is a bit more complex, that uses:

=ROUND(365*(Bxx-INT(Bxx)),0)+1

ROUND(X,Y) rounds the number X, to the number of digits Y, for example ROUND(12.347386,2) produces 12.35 as a result. Bxx-INT(Bxx) simply removes the whole number part of a number leaving the part to the right of the decimal point, for example 12.347386 becomes .347386. This is then multiplied by 365 because it's a fraction of one year, and one is added because the first day of the year is '.000'.

The above formulae makes two columns, one of year and one of date. These are combined in column DateNum (I) to make a date index, which is essential to the operation of the whole spreadsheet. The combination is simply done by =Gxx&"\"&Hxx, which concatenates to make the string seen in column I, e.g. 1979/4.

Area is simply copied in cell by cell, to do this in a new table (Insert > Table) one need only put the formula into the first table entry, when the table is resized by clicking on a lower corner and dragging down, the formula copies down that column.

In Extent and Volume (K & L) we see the first use of the key formula behind the whole sheet; the VLOOKUP function. I'll ignore Extent as that's not maintained, and once you understand how volume works you'll be able to start maintaining Extent should you wish to. In a simlar way to the DataTable table, volume is built into a series in a volume table, which is seen in columns U and V. The values in this table are then referenced in column L, the volume column of our main data table.

VLOOKUP works as follows:

VLOOKUP(Reference, Source Data, Column Number, Match Type)

• Reference - the item VLOOKUP will search for in the source data.
• Source Data - the range of the spreadsheet that VLOOKUP will search in, actually it looks down the first column of that range.
• Column Number - the column number in the Source Data range that VLOOKUP returns a result from.
• Match - the type of match, we want to use an exact match so we'll use FALSE for this.

Because we'll be adding new data to the sheet the best was to declare our source data is with a named table. This is why the DataTable table has been built up. VLOOKUP will search down the DateNum column (I) until it finds a match for the Reference it's looking for, then it will use Column Number to tell it how many columns from the first column of the Source Data range it should go (to the right) to find the value to display.

So in L11 we have:

=VLOOKUP(I11,ProcessVol,2,FALSE)

This means - look for the value in I11 in the table process volume, and when you find a match go to the same row as the match, but get the value from column 2. Because FALSE is selected get an exact match, or return an error. In this way the DateNum index of the Volume table (U & V) is searched for the dates listed in the DateNum column of DataTable and the relevant values of volume are copied into the DataTable. Once again; as you resize the table downwards the VLOOKUP formula copies down, so every date gets the correct data.

The result is a table of dates, and for each date are the matched Area (Extent) and Volume, although I'm not using Extent further.

But a list of numbers is not really of use, what we need to do next is get these values into a useable form. I'll go over that in my next comment.

[Jim Hunt]

Hi Chris,

This looks like turning into an extremely useful thread! Thanks for sharing your sea ice spreadsheet with the world.

One thought strikes me at the outset though. Personally I much prefer Scientific Linux to Windoze. Any chance you could distribute it in an alternative format?

[ivica]

Quick thought:
Maybe Apache OpenOffice Calc opens those Excel files w/o problem?

[Jim Hunt]

In all the circumstances I was wondering about possibly a different model. A Google Apps sheet for example?

[Pmt111500]

*.csv -versions of excel files could be appreciated by some, too. LibreOffice at least makes some errors with excel-files and the same was the case with OpenOffice, when I used that to do some graphs of cryosphere today data files a few years back. it's indeed tedious to go through everything manually, just to find out the leap year issue, still not sure if I corrected it correctly (using the standard year lenght 365.25 divisor for the 366 and 365 day years separately, if I remember correctly)

[ChrisReynolds]

Well I could save as separate sheets, but Excel allows you to use sheets, the whole shebang works on discrete sheets interacting, so saving as separate CSVs or sheets really would be a pain. I am rather busy. Bear in mind that when I have to do the monthly update I'd have to update the whole lot.

I've just checked and Google Docs can't open it as the app used is Google sheets - the hint being in the name, as I suspected it can only use sheets, not workbooks containing interlinked sheets.

If people not using Excel haven't got alternate ways to tabulate data I could give some separate CSV files as a starter one off set. Then it would be up to people to update the data in those files as they see fit.

Would that be a reasonable compromise?

I could suggest you just get Windows and MS Office. 

Regards leap year, because PIOMAS ignores it I've ignored it, the method I use allows for that, no tedious messing around in the final table.

[Pmt111500]

heard and understood, stupid of me to ask, also, .csv's wouldn't keep the formulas used intact. Libre Office made fewer mistakes than the OpenOffice, but in both cases Excel-graphs got messed up and all the tables should be checked for errors (including formulas), they may look intact but the formulas may be not the same... (proprietary codes are sometimes annoying)

[icebgone]

Chris, Your explanations have triggered some old memories of my days manipulating financial information.  I look forward to further posts.  I am available to help part-time with inputting if the need arises.  It has been 10 years since I last looked at a Pitot table and used a four way split screen to watch data flowing into the final presentation.  I would have to brush up on my If-then and other excel short cuts in order to be really useful.  Think I'll visit my local library and reread Excel again.  Maybe its time to lose some of the rust in my brain.

[Jim Hunt]

Hi Chris,

As you may have gathered by now, I'm something of an "open source" fan. As Kim put it recently "All science should be conducted this way!"

Actually I was rather hoping my suggestion might in fact reduce your workload. Using Google Apps people from around the planet with access to a web browser but no significant disposable income could view and even contribute to a collaborative project.

I'm in the fortunate position of being able to afford Windows/Office, though I'm stuck at XP/2003 respectively! The interdependent sheets is a bit of a gotcha though.

[ChrisReynolds]

I don't think there's need for much collaboration, beyond the discussion that takes place here. The workload isn't an issue; through the winter I update once a month, later in the summer I may update CT area on a weekly basis.

Really the spreadsheet is just a way of tabulating the data we normally get in a series format to make the process of further analysis more easy. I'm aware that there are probably people out there with Excel who wouldn't be able to easily tabulate the data. I'm just making things easier for them by offering a cut down version of my main sea ice analysis spreadsheet. Just like I provided the thickness/volume breakdowns over at my blog. It's stuff I already have that might help others.

The gridded PIOMAS stuff was different, there I started off trying to write an app for Excel that would enable anyone to process the data to answer their own questions. But I rapidly found that this was too difficult - I couldn't cut the problem such that an all-purpose solution fell out neatly. Basically I now find each question needs a specifically tailored solution. I do intend to do a thread on getting the gridded PIOMAS data into Excel. However that will basically be a case of - here's the subroutines, here's how they work, now you can get a set of arrays in VBA - any number of data arrays and for the whole grid a shadow array of grid locations and areas. After that it's up to people what they do with it. This is a similar approach.

I guess I should be more enthused by collaborative work, but in my hobby I tend to follow what interests me.

[Jim Hunt]

Hi Chris,

Fairy nuff. I'll shut up forthwith, and let you get on with it!

After that it's up to people what they do with it

Is it OK with you if others of us use all the stuff you're providing for our own purposes? Personally I prefer databases to spreadsheets for example, and Arctic.io seems to be sharing some cunning techniques for automatically converting data into attractive web pages.

[ChrisReynolds]

If I wasn't happy for people to use things I wouldn't put them on the 'net. 

[ChrisReynolds]

Excel - Tabulated Data.

So far we have a table of sea ice area and volume, every day since 1/1/1979 in order. But what we need to do to get anything useful from the data is tabulate it by year and day. Once we have a table of years by days we can do things like work out anomaly from the long term mean and if we want to know the ice state for the same day in past years, that's easy too.

The way to do this has already been shown in the comment above, VLOOKUP is the way to do it.

Moving on to the Area sheet you'll see a table with years along the columns and day numbers down the rows. There's also a daily average, which as I'll show is used to make the anomalies from the average.

Much of that table of numbers is simply figures, this is because once I'd got the figures in there, I copied and pasted the values for past years to reduce Excel calculation time, there being over 12000 cells of data per table this was becoming significant (my main sea ice analysis spreadsheet has about 10 sheets). Anyway, if you scroll across to column AK in sheet Area you'll see VLOOKUP appearing again in 2012 and 2013.

In this case the daynumbers down the side and year along the top is used to make the date that will be found in the RawData sheet's DataTable. So the form of VLOOKUP is shown below, using the contents of cell AK11:

=VLOOKUP(AK$9&"\"&$B11,DataTable,2,FALSE)

The first part AK$9&"\"&$B11 takes the year from the top of the table and the month at the left edge of the table to create a string YYYY/DD, this string is then used in DataTable on the RawData sheet, described in the previous comment (start of thread), to get the 2nd column in DataTable, which is area.

Now click on the Volume tab and you'll find similarly tabulated PIOMAS volume data, going again to AK11 we have:

=VLOOKUP(AK$9&"\"&$B11,DataTable,4,FALSE)

Note that the only difference is that this lookup returns the value in column 4 of the line that the date index matches. Checking out DataTable on sheet RawData and it can be seen that volume is the 4th column - hence the above VLOOKUP statement returns volume.


Now to say something about filling in data. You can see that the item sought in DataTable is stated as AK$9&"\"&$B11 in two different sheets, because this statement relates to the sheet on which it appears it needs no qualification to tell Excel which sheet to use.

However if you click on an empty cell, say on sheet Volume, and press '=' then click on an adjacent cell you get a reference to that cell. As you have Excel you know this already. But a lot of people don't use sheets much if at all. If you enter '=' then click on one of the tabs to bring up another sheet and click on a cell you get something like =Area!Y11 which means the cell that's put into contains what's in cell Y11 on sheet Area.

There are, as I said earlier, over 12000 daily values of area and volume, so obviously I didn't type in VLOOKUP functions in every cell. Excel can auto fill, if you copy or 'fill down' (press control D below the first cell you want to fill from) you can copy down the same formula and it increments the target. Going back to =Area!Y11, put that in an empty cell on a new sheet or in somewhere blank, as you probably know if you highlight cells below it you can select CTRL + D and it fills in, so you get not only what's in sheet Area cell Y11, you get the contents of Y12, Y13, Y14 etc on sheet Area. You can do the same thing with copy and paste, if you copy the column of numbers you just filled down and paste next to it, you get the next figures along on sheet Area.

But you may want to change the way Excel increments. To fill in all those VLOOKUP functions you start in the top left corner, you can start elsewhere and copy/paste, but if you need these instructions we'll keep it simple for now. If you fill down, or copy across, you'll find that when the new formulas are automatically entered you get errors, because instead of referencing the left most column for the day number and the top most row for the year, that reference moves as you paste.

There is a way around this and it's used in the VLOOKUP functions used earlier in this comment. In the reference that builds the date number to be searched for there are $ signs:

AK$9&"\"&$B11

AK$9 means when EXCEL changes this cell reference (the Year) as it's moved around, it changes the column reference (AK) but does not change the row ($9). $B11 means when EXCEL changes this cell reference (the Day) as it's moved around it does not change the column reference ($B) but does change the row (11).

In other words the $ sign means do not change. This means that as you copy/paste or fill the VLOOKUP function around the sheet the year moves on as you enter new VLOOKUPs into new columns but always points to row 9, and the day changes as you fill down but always points to column B. Using a Table for the raw data (DataTable) means we don't need to mess about with the references, but if you were using row/column notation you'd have to fix both row and column, otherwise the source table would move down as you copied down.

In my next comment I'll go onto things you can do with this tablulated data, and how to make drop down list selection that changes the data you're looking at.

Note; there may be some general chat around this series, you can spot these comments by the bold heading.

[ChrisReynolds]

Excel - Using the tabulated data.

So far we have a central page for updating everything with new data (Sheet RawData), and a way of tabulating that data into consecutive years because the massive seasonal cycle in Arctic sea ice makes a simple timeseries of little use. So how can we use our year/day table of data?

If you go to sheets Area or Volume, and look at the top few lines above the Year/Day table, you'll see some more numbers with headings in  column C. Here the maximum and minimum values (area or volume) and the respective day numbers are worked out.

For the Area sheet: First I get the maximum or minimum volume by using the following formulae searching across the whole of the year. For example for the maximum I use:

=MAX(D11:D375)

If you click on cell D3 and press F2 it will show the formula and highlight the cells in column D that this formula applies to. Note that there are no $ signs because all I do with this is copy it across, so it gives the maximum for each year. This formula returns the highest values in the range selected, I can then search the range to get the position of this maximum value.

=MATCH(D3,D11:D375,0)

The final zero simply means 'get an exact match'. Because this position is with respect to the first day of the year the number returned is in fact the day of the year. Now by simply highlighting along and selecting Insert > Charts, you can make a graph showing day number or magnitude of maximum or minimum.

You can also do this in the table of area or volume, say you're at day 156 and you want to know how the ice area or volume is faring with respect to the same day in pervious years you simply highlight across and us Charts to make a bar graph or line graph. Doing the same manually with a daily series from 1979 onwards would not be the sort of thing you'd attempt.

Having things in order also makes other calculations a breeze, for example say you wanted to look at the change in area or volume from June 1st to July 31st, you'll find an example of such a calculation around cell X381 on the Area sheet.

There is another way to select data, I'll go into that in the next comment, although for the moment if you go to sheet Monthly and scroll across to AN3 (to the right of the PIOMAS volume table) you'll find a user selectable graph: Select the years from the drop down lists (years in bold) and you'll see that the numbers change and with them the graph changes.

The seasonal cycle is a problem in analysis, the most straightforward way to deal with it is to use anomalies.

Anomalies are the difference from the long term average. You can see where I've calculated the average on sheet Area column C, I've simply used the formula =AVERAGE() to calculate the average for each day from 1980 to 1999, a 20 year baseline average that is before the most aggressive phase of ice loss (because it's the changes recently that interest me most). But you can use any reasonably long period, I'd suggest 20 year minimum, and simply copy down the average formula for all 365 days to give you the average for each day. This gives the average season cycle for the baseline period (in this case 1980 to 1999).

If you scroll across to columne AS you can see where I have calculated the differences from that baseline average. I've used the calculation =AL11-$C11, in other words 'daily value minus long term average for that day'. Note that I've used a $ sign to keep the column for the average as column C, so that when I copy across it's always subtracting that column, not following years daily values. But I've not used a $ sign on the row number because I want the days to change as I copy down so I'm always subtracting the average for that day. This has also been done in an identical manner on the Volume sheet.

So now we have removed the average seasonal cycle we can use graphs to examine the changes in the seasonal cycle, I've done such graphs for area and volume on their respective sheets.

Before finishing these two comments there's a little trick you might find useful. Above I pointed you to a neat way to select data, I'll go over that in a few days. But Excel also allows you to do a similar thing with graphs.

If you go to the Area worksheet and look at the graph of recent years, this shows the whole cycle for 2000 to 2013. Click on the edge of the graph and you'll find that the graph activates as the main object, but also in the background there are now green boxes around the years, and a blue box around the source data. At the corners of the box are small filled squares, click on the left upper corner and drag it to the left (not up) until it covers the data from 1997. Now look at the graph, you've automatically included those three extra years of data without any hassle at all.

Excel is so cool! 

[ChrisReynolds]

Selecting a Subset of Data.

In my earlier post I mentioned a way to select data, in this post I'll outline two instances of such an approach, one simple, one complex.

First the simple example. The source spreadsheet is available in the first post of this thread, open that spreadsheet by saving to your PC and opening in Excel rather than the GoogleSheets viewer.

Going to the Monthly sheet (bottom row of tabs), go to cell AN:3 on that sheet. Here you'll find some columns of data and a graph. The years are embolbened on row 5. Click on one of these and you'll find you get a drop down list of years, if you click on a new year and watch the graph you'll find the graph changes. How is this done?

The drop down lists are made using Data > Data Tools > DataValidation. Click on Data Validation and on the drop down select Data Validation, on the Settings tab select Validation Criteria > Allow > List to set the cell so that only values in a drop down list are allowable. The Source range selector box is where you select a list of values allowable in the cell you are modifying. In the three drop down lists of years, I've used the horizontal list of years (values allowable) above the table of monthly PIOMAS volume. Otherwise in an out of the way place on the sheet I could make a list.

To make these years change the graph according to what year is selected, the graph is simply set up to use the three selectable columns below the years selected drop down lists. How the graph changes is actually implemented in these columns of data. Note that as well as the years along the top that table has months (1 to 12) down the side.

The column uses the =OFFSET() formula. The syntax for which is:

=OFFSET(reference, row, column)

Reference is the cell from which the offset operates with respect to, rows is the number of rows to go right of the offset, columns is the number of rows to go below the reference. If you click on cell AO:6 and press F2 you'll get a clue as to what's going on. Cell $C$6 is the reference, the $ signs are used to stop Excel from changing the cell location as the formula is filled down or copied across. You'll note that $C$6 is the January 1978 cell of the table of PIOMAS volume.

The Row part of the formula is written as $AN6-1. That's $AN to stop the column changing as the formula is copied across, but the 6 doesn't have a $ because we want that to refer to different rows as we fill the formula down. We subtract 1 from the contents of that cell, the month, so that for January there is no offset (1-1=0), but for example; for April there is an offset of three rows below cell $C$6 (4-1=3).

The year is selected using the column part of the formula. Here the cell reference is written as AO$5, so that the column (AO) can change, but the row always stays pointing to the row of years. 1979 is subtracted from the year referenced so that the OFFSET formula references no offset for year 1979.

So when you change the year the OFFSET formula, as copied down in the table, goes to the start of the PIOMAS volume array and returns the column corresponding to that year. When you select a new year from the drop down list the relevant year is selected from the source data, the monthly PIOMAS table.

This isn't only useful for graphs. Often you would make columns of data and process in columns to the right. But sometimes you might want an overview of various statistics for one year, month, or day. You can use this method to select data and all the calculations you make from that data automatically change as you select new data.

This basic approach can be expanded to make multiple selections. If you download and open the following spreadsheet you'll see how to use multiple selection criteria.
https://docs.google.com/file/d/0B3pB-kdzoLU3MkNsZFpxZTlmUEk/edit?usp=sharing

Go to cell CI74 and you'll find two selection lists; one form Region, one for Month. This allows you to select the region and month from the whole set of data from 1978 to make analysing of the data easier. If anyone wants me to explain how that works, just ask. But it really isn't beginners stuff, so is something that can wait until later.

[ChrisReynolds]

Excel sea ice spreadsheet updated with new data to current date.
https://docs.google.com/file/d/0B3pB-kdzoLU3d3VRdC1oYnlucms/edit?usp=sharing

Do not wait for Google Docs to present a view, it's an Excel format multi-sheet format file which cannot be rendered by Google Docs. Click File>Download, save and open.

[Pmt111500]

I don't know if .ods (open document spreadsheet) translates from finnish to english version neatly, attached is one of my files I've used on CT SIA. There should be several tables in various forms and a couple of graphs previously unseen here (one of which is possibly wrong, but I really would prefer if everyone who is interested in calculating these himself would do so from the original data.

[ChrisReynolds]

Updating the Spreadsheet.

As I've said over at my blog recently, I'll update the spreadsheet regularly. However for those using it who want to do so themselves, here is a rundown of how to update with new data. This assumes you know basic useage of Excel, such as selecting and expanding selection down.

In my previous comment you'll find a link to the spreadsheet.

Once again, on the bottom of the sheets are tabs to select individual sheets. The source of the data is the RawData sheet, from this the other sheets draw their data. So this is the sheet I'll start from when updating. But before updating select Formulas from the option tabs at the top of the Excel screen, on the right you'll find Calculation Options. Because of the time involved waiting for Excel to update the sheet you'll need to click on Calculation Options and select Manual, this means that Excel will not try to update after every change you'll make.

Now select the RawData sheet. You'll find I've used 'split screen' to keep the most recent data at the bottom of the sheet visible. The URLs for the data are given on the sheet. First I'll describe how to update Cryosphere Today Area (CT Area).

Open up the data page for CT Area in your browser, and go down to the bottom (CTRL+End). Going back to the spreadsheet, column B contains the date index from the CT Area data, note the index of the last day in column B. Now go back to the CT area data and find the day after the last date in column B of the spreadsheet. For example today I find the last time I updated was to 2013.3716, so going down the first column on the CT Area data webpage I find the day after is 2013.3754.

Select all data from the first day that's not in the spreadsheet and copy, then paste from the first empty cell in column B. Now you'll need to get the data into the right columns, Select the option tab Data in Excel and click on Text To Columns. A dialogue box comes up and you'll need to select the 'fixed width' option. Click next, but at the final screen do not select 'date', select the 'general' data type option (which should already be selected).

Volume is filled in using the same technique using the volume data starting in column P, but that data is copied into a table (blue shaded region to the right. So once you have the volume data in you'll need to pull the table down. To do this hover the mouse over the lower right corner of the table until the mouse cursor becomes a diagonal double headed arrow. Now click and drag the table down until it's level with the data you pasted into columns P to S.

Now you have the area data in the sheet, and if doing so; the volume data. The next stage is to update the main data table in columns I to L.

In columns G and H select the last two cells with data in them. Then move the selection down to the same level as the last data in columns B to E. This is done because the CT Area data date index is used to generate the date index for the master table. Now that you have a series of cells highlighted use CTRL+D to fill down the formula used to seed the date index. You'll find the the date index remains the same as the last pre-existing entry in columns G and H.

Now to update the master table in columns I to L. As with volume, hover the mouse over the lower right corner of the table until the mouse cursor becomes a diagonal double headed arrow. Now click and drag the table down until it's level with the data you pasted into columns B to E.

If new volume data isn't out, and you're just updating area, don't worry about the #N/A entry under volume, as with Extent in column K, this happens when there's no data available.

The RawData sheet is now updated, so you can go back to Formulas>Calculation Options and select Autumatic Calculation, key F9 will do a onne off update but will leave the sheet in manual calculation mode. The update may take a minute or so.

Now you're in a position to update the sheets you use, doing so for volume and area is the same, so I'll just describe updating the area sheet.

Clicking on the Area tab to select the area sheet, go along to column AL and scroll down to the end of the series of figures for 2013. Click on the last figure, and holding down the left mouse button drag the mouse down so as to highlight the empty cells below (you could alternatively use CTRL + DownArrow). Release the left mouse button and use the key combination CTRL + D, to fill down. I usually fill down further than I think I need to, then delete the cells containing N/A, to ensure I have all the data. Now you should find that the latest data has been filled onto the spreadsheet.

The same technique is then used to fill in any further data on that sheet. For example, go across to column CB and use fill down to fill the CT Area anomaly data. If you fill down too far you'll find a jump to large numbers. This is simply the baseline seasonal cycle emerging, as you've gone below the current data.

This is the last in this series and by following these posts you should now be in a position to maintain the spreadsheet, and to develop it further to begin to answer questions you have about the sea ice.

[ChrisReynolds]

I don't know if .ods (open document spreadsheet) translates from finnish to english version neatly, attached is one of my files I've used on CT SIA. There should be several tables in various forms and a couple of graphs previously unseen here (one of which is possibly wrong, but I really would prefer if everyone who is interested in calculating these himself would do so from the original data.

It does open in Excel after Excel has done a 'repair' action. The graphs are lost though.

Sorry for not catching your post earlier, I don't check here regularly.

[Pmt111500]

hope the simple formulas/cell backgrounds(I've used this to highlight the average lines (every seventh) do show up correctly though! I've just used copy/ paste special (only numbers) to move the numbers between sheets. the first sheet has the full record of CT Area divided in years and weeks. the melt/gain is calculated from this. so when updating, add just the numbers lacking from the 2013 column, then there's some moving about the numbers and averages and checking how far the formulas are defined.

[ChrisReynolds]

I do recall weekly yellow bands. But must admit I didn't look too closely at it. However there was only one sheet, I'm pretty sure there weren't multiple sheets.

[Pmt111500]

Thanks for the info. So Excel does not get the 3 or was there 4 other sheets of *.ods.

BTW, tried to download your *.xlsx file to libreoffice, and it didn't understand it. :-(. On the other hand, the *.xls files I had previously, have opened quite ok, but the links between spreadsheets have been broken and I've had to redo most graphs in those (not ASI-related files)