Excel Formulas Not Calculating

Problem Statement

Some formulas on an nVision instance using Excel 2010 are not being calculated when the report is run. Calculation Options are set to "Automatic" so all formulas should be calculating. Recalculating the spreadsheet using F9 does not resolve the issue.

This issue started to happen on some reports, but not all, once the users had been upgraded to Excel 2010 on Windows 7 machines. It occured with PeopleTools 8.51 running in Excel compatibility mode with layouts created in Excel 2003 and below, and also after upgrading to PeopleTools 8.52 with the layout upgraded to Excel 2010.

My research online indicates that this issue sometimes occures with Excel 2010 when the spreadsheet was first created in "legacy" Excel (Excel 2003 and below). All longtime users of PeopleSoft nVision will be in this situation. The issue is an Excel 2010 issue, not an nVision issue per se.

Resolution

I discovered that when I changed the formulas from addition formulas using + to forumauls that used SUM this resolved the issue. For some unknown reason, once I did this, all the addition formulas across the rows to the right of the formulas I updated that weren't calculating before, now caclulated fine without having to change them. I had to change all formulas down the entire first column in all the rows where the issue was occuring.

Workaround / Quick Fix

•  If your layouts have not yet been upgraded from old Excel (Excel 2003 and below - .xls nVision instance files), then once the report instance has been generated, "converting" the instance to Excel 2010 results in all formulas being calculated correctly.
  
  To convert the instance from .xls to .xlsx (or .xlsm if there is a macro attached to your layout), navigate to File and click the Convert icon.

Convert old Excel format to Excel 2010 format by clicking the Convert icon under the File > Info menu

• Many of you will know that if auto-calculation has been disabled, you can recalc your spreadsheet by typing F9. However, F9 only recalculates formulas that have changed, or for which the data in dependent cells has changed since the last time the spreadsheet was calculated. That being the case, F9 will not resolve this issue.
  
  However, Ctrl + Alt + F9 recalculates all forumulas whether they have changed or not. I found that this resolved the issue.