Formulas in Excel spreadsheet do not calculate until “Enable Editing” is clicked

Question

Yergamoni, Rajkumar 1

When export the excel, the formulas was not calculating until “Enable Editing” is clicked in Excel spreadsheet.

Am using below code for cell formula:

            var cellFormula = new CellFormula();  
            cellFormula.Text = value?.ToString();  
            result.CellFormula = cellFormula;

Farid Uddin Kiron MSFT 456 Reputation points Microsoft External Staff

2022-07-25T09:44:05.25+00:00

Hi @Yergamoni, Rajkumar Have you callled the function worksheet.Calculate() before you save the document? Could you please try that and share if you encounter any further issue.
Yergamoni, Rajkumar 1 Reputation point

2022-07-25T10:19:23.353+00:00

Whenever trying this Calculate() method, I couldn't able finding that method.

FYI :- I am using DocumentFormat.OpenXml.Packaging package.

Thanks...
Roman Rozhanskyi 1 Reputation point

2022-07-26T09:41:53.743+00:00

Hi @Farid Uddin Kiron MSFT , I have the same issue. I've tried to use the worksheet.Calculate(), but cells with formula have value "#VALUE!" (cell F1). I've attached a screenshot with an example. If the formula is "simple" (for instance just a Sum() like in cell G104) then it works correctly. It looks like the issue is related to OFFSET function because without OFFSET function works as expected.
Logan4886 1 Reputation point

2022-07-27T17:24:53.263+00:00

hi, have you fixed this issue. because I have made an excel sheet for saving some important codes of gb whatsapp lite but not calculating the proper data. I tried different formulas but nothing is working. As also downloaded the sheet from google so that I will use the template but when I enable editing mode so same the case showing the same issue.

Your answer

Farid Uddin Kiron MSFT 456 Reputation points Microsoft External Staff

2022-07-25T09:44:05.25+00:00

Hi @Yergamoni, Rajkumar Have you callled the function worksheet.Calculate() before you save the document? Could you please try that and share if you encounter any further issue.
Yergamoni, Rajkumar 1 Reputation point

2022-07-25T10:19:23.353+00:00

Whenever trying this Calculate() method, I couldn't able finding that method.

FYI :- I am using DocumentFormat.OpenXml.Packaging package.

Thanks...
Roman Rozhanskyi 1 Reputation point

2022-07-26T09:41:53.743+00:00

Hi @Farid Uddin Kiron MSFT , I have the same issue. I've tried to use the worksheet.Calculate(), but cells with formula have value "#VALUE!" (cell F1). I've attached a screenshot with an example. If the formula is "simple" (for instance just a Sum() like in cell G104) then it works correctly. It looks like the issue is related to OFFSET function because without OFFSET function works as expected.
Logan4886 1 Reputation point

2022-07-27T17:24:53.263+00:00

hi, have you fixed this issue. because I have made an excel sheet for saving some important codes of gb whatsapp lite but not calculating the proper data. I tried different formulas but nothing is working. As also downloaded the sheet from google so that I will use the template but when I enable editing mode so same the case showing the same issue.