The Govmt Changed Data for 2016, 2017 in a Large Spreadsheet. How Can I Determine What Changed? 2

[racookpe1978]

The government's NSIDC (National Snow & Ice Data Center) has changed hundreds of Daily Sea Ice Area and Sea Ice Extents values for 2016 and 2017, but they've not said why, nor what records have been erased, and what records have been changed.

Link to their latest Regional Sea Ice spreadsheet is here:

https://nsidc.org/arcticseaicenews/sea-ice-tools/

Specifically, Item 6. Daily sea ice extent, by region (Sea_Ice_Index_Regional_Daily_Data_G02135_v3.0.xlsx)

I will upload the 2018-07-24 version, and the original daily values for 2016 and 2017 in a few minutes.

Problem Statement.
I have been tracking the daily Sea Ice area and extent changes for several years now, looking for patterns and trends. Clearly, you don't know the specific numbers nor trends, but then again, you don't have to for this question.
However, somewhere between 11 July 2018 and 23 July 2018, the NSIDC zero'd out (reset to zero) the summer values for Sea Ice Area and Sea Ice Extent for July, August, and Sept 2016 and 2017 that had previously been recorded. Some Daily sea ice area values have both gone up with this revision, but the majority have been adjusted down (sea ice areas have been magically reduced for the previous two years.

The NSIDC provided no note, comment, consistent spreadsheet Rev Nbr, nor front page explanation for this change.

Understand that I only track 4 regions at latitude 60 closely (Bering Sea, Hudson Bay, Sea of Okhotsk,and Gulf of St Lawrence) but the NSIDC provides Standard Excel Tabs for those 8 tabs, plus some twenty others. Comparing even a few columns for the 8 tabs was tedious, requiring a lot of manual editing. And, obviously, every manual edit of every column introduces the probability of my own manual errors.

I have found no changes prior to 2016 in any record that I have looked at in the eight specific Tabs for Extent and Area for those four seas listed - but I am very suspicious of any changes so limited. (Prior to the 23 July 2018 revision, these records showed politically incorrect and embarrassing record high sea ice levels for the summer 2016 and 2017. The official records have now been "conveniently" erased and reduced to show continuous sea ice decline through both years.)

Is there any way I can "subtract" the data value for every tab of the two spreadsheet versions (and generate a third spreadsheet ??) containing only the differences between equivalent values for each date?

 

[racookpe1978]

The NSIDC's 2018-07-23 spreadsheet version is attached.
The Daily values for year's 2016 and 2017 have been revised for the Bering Sea, Hudson Bay, Sea of Okhotsk, and St Lawrence Gulf, but what else has changed?

 

[racookpe1978]

The original 2016 and 2017 daily records are found in this archive version of the same spreadsheet from 2018-04-30.

Obviously, 2018 daily records are changed between April and now, but the prior year daily values cannot change without some justification or notification.

 

[IDS]

Is there any way I can "subtract" the data value for every tab of the two spreadsheet versions (and generate a third spreadsheet ??) containing only the differences between equivalent values for each date?

Sure:
Open a new spreadsheet
Enter a formula in cell A1, subtracting the A1 values in the other two sheets
Edit the formula to change $A$1 to A1 for both sheets.
Copy the formula as far as you want.

If the cells contain strings this will return #VALUE. To avoid that you can add an IFERROR:

=IFERROR('[book1.xlsb]Sheet1'!A1-[book2.xlsb]Sheet1!A1,"")

You could also try contacting the site, and ask them why they have changed the data.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

Example for St Lawrence data attached.



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

Hi,

These tables are typical of non-computer types, but in a way might be better for what you want to do.

I opened each workbook and a third new workbook. I noticed that...
1) there are 366 rows of data, one row for each day of the year.
2) 2018 is in AQ

On the first sheet of the new workbook. I'd name the sheet Baffin-Area-km^2'

[tt]
AQ2: =INDIRECT("'["&FName1&"]"&MID(CELL("filename",AQ2),FIND("]",CELL("filename",AQ2))+1,255)&"'!"&CELL("address",AQ2)) - INDIRECT("'["&FName2&"]"&MID(CELL("filename",AQ2),FIND("]",CELL("filename",AQ2))+1,255)&"'!"&CELL("address",AQ2))
[/tt]
...where FName is your base workbook file name and FName2 is the current workbook with deviant values

...and COPY n PASTE to row 367 and over to column C.

This formula incorporates the SHEET NAME and cell values for each corresponding cell in that sheet from the two workbooks.

I wrote code to 1) add sheets to the new workbook and name them in accordance with Workbook 1

Then COPY the data from sheet1 to each sheet in the new workbook.

Sub [COLOR=#204A87]AddSheetsAndName[/color]()
    Dim sName As String, wb As Workbook, ws As Worksheet
    Dim iCnt As Integer
    
    sName = "NSIDC_Sea_Ice_Index_Regional_Daily_Data_G02135_v3.0-3_04-30-2018.xlsx"

    Set wb = Workbooks(sName)
    
    For Each ws In wb.Worksheets
        If ws.Name <> "Documentation" Then
            iCnt = iCnt + 1
            With ThisWorkbook
                If iCnt > .Worksheets.Count Then
                    .Worksheets.Add After:=.Worksheets(iCnt - 1)
                End If
                .Worksheets(iCnt).Name = ws.Name
            End With
        End If
    Next
End Sub

Sub [COLOR=#204A87]CopyFirstToAll[/color]()
    Dim ws As Worksheet
    
    For Each ws In Worksheets
        If ws.Name <> Worksheets(1).Name Then
            Worksheets(1).UsedRange.Copy ws.Cells(1, 1)
        End If
    Next
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[dik]

Doug and Skip... great posts, Dik

 

[cowski]

Depending on the version of Excel you are using, you might already have a built-in compare tool:

https://support.office.com/en-us/ar...-compare-f2b20af8-a6d3-4780-8011-f15b3229f5d8

 

[racookpe1978]

I greatly appreciate everybody's replies and help - Been fighting travel and funeral arrangements, and have not downloaded and updated my directories yet. Robt