excel - Why won't this formula calculate unless i double click a cell?

Question

See this .xlsx file. No macros, of course!

When you open the file, you will see the following cells:

[Series     ]
[10         ]
[20         ]
[30         ]
[           ]
[           ]
[=SUM(B3:B7)]

The sum does not calculate unless you double click, and then press enter, in one of the bottom three cells (eg two blank, =SUM(B3:B7))

---

Weirder still, once you 'kicked the formula into life' if you save the workbook and reopen it, it behaves fine.

I've tried changing the extension to .zip, and playing around with Xml to see if I could identify the changes made by saving it after it's started working, but I could only see that a version number had changed.

We. Ird.

---

Also, I tried saving this in an earlier Excel format, and the problem remained.

---

I just want to reiterate - the attached spreadsheet is a .xlsx file, and therefore does not have any macros! There really is no other way of explaining this problem without the attached spreadsheet. Hopefully, by putting 50 points of reputation on the line, someone will trust that I'm not a script kiddie and have a look!

---

I created the file using EPPlus, and the following C# code:

    public void Generate(string outputPath)
    {
        using (FileStream fsTemplate = new FileStream(TemplatePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
        {
            using (FileStream fsReport = new FileStream(outputPath, FileMode.Create, FileAccess.ReadWrite, FileShare.None))
            {
                using (ExcelPackage ep = new ExcelPackage(fsReport, fsTemplate))
                {
                    using (ExcelWorksheet ws = ep.Workbook.Worksheets[TemplateSheetName])
                    {
                        //
                        int iHeadingsRow = 2;
                        int dataRowIndex = iHeadingsRow + 1;
                        //
                        List<decimal> list = new List<decimal>() { 10, 20, 30 };
                        //
                        foreach (var number in list)
                        {
                            ws.Cells[dataRowIndex, 2].Value = list[dataRowIndex - iHeadingsRow - 1];
                            //
                            ws.InsertRow(dataRowIndex + 1, 1, iHeadingsRow + 1);
                            //
                            dataRowIndex++;
                        }
                        //
                        ep.Save();
                    }
                }
            }
        }
    }

I would like to emphasise that this is not an EPPlus issue! This is a common problem with Excel opening a valid workbook, and refusing to calculate a formula. Formatting the cells as numeric makes no difference. I have been using EPPlus to create working Excel files for months, and I have not installed any updates to EPPlus, so it must be problem with Excel, right?!

The solution must lie in the difference between the original file, and the version that has been kicked into life and then saved. I just can't seem to pinpoint exactly what that difference is...

---

The =SUM(B3:B7) is already present in the template file, along with the text 'Series'. The code only adds the {10,20,30} items into the spreadsheet.

Answer 1

You are adding data to an already existing Excel sheet which already contains the =SUM(B3:B7) formula.

I guess that is an EPPlus issue. Namely, the issue is that you are not calling the Calculate() method after entering the data:

... you can let EPPlus calculate the results of the formulas in a workbook.

This is done by calling the Calculate() method, which is available on Workbook, Worksheet and Range level. When Calculate() is called EPPlus will evaluate the result of the formula and store the result as the Value of the cell - just like Excel do.

After you type a formula in Excel, Excel calculates the value and stores it in the file together with the value.

For example you have a sheet like:

100
200
=SUM(A1:A2)

Excel stores this (I removed the non-relevant XML attributes):

<sheetData>
    <row r="1">
        <c r="A1">
            <v>100</v>
        </c>
    </row>
    <row r="2">
        <c r="A2">
            <v>200</v>
        </c>
    </row>
    <row r="3">
        <c r="A3">
            <f>SUM(A1:A2)</f>
            <v>300</v></c>
    </row>
</sheetData>

When you insert the data to the XLSX file externally without the value, Excel does not have the cached value stored yet and displays 0. Your XLSX file (the one which you uploaded) contains in sheet1.xml:

<row r="8">
    <c r="B8">
        <f>SUM(B3:B7)</f>
        <v>0</v>
    </c>
</row>

There is some explanation in the similar Java library Apache POI:

Sometimes Excel will notice itself, and trigger a recalculation on load, but unless you know you are using volatile functions it's generally best to trigger a Recalculation.

and there is a similar problem Excel cell displaying zero instead of a calculated / referenced value from the Java perspective.