Hi, just as Excel 1902 is being rolled out in my organisation, I discovered a very nasty calculation bug which is having a major business impact.
This occurs when opening a file which was made with any previous Excel release (I tested it back to Excel 2010) up to Excel Version 1808 on Windows 10 is now opened using Excel 1902. Some cells using a LOOKUP formula are returning a #NA result straight after opening the file.
I also found a way to reproduce this in a new file / with new cells.
How to reproduce on a new file:
- Put value "ABC" in A1 and in A2
- Put the following formula in A3: "=LOOKUP(2,1/(A1:A2<>""),A1:A2)" --> the purpose of this formula is to repeat the last non blank item from the referred set of cells
- Drag this formula down from A3 down to A10
- You will see that all cells from A3 to A10 repeat the value "ABC"
- Clear the content of one cell in this list (A7 by instance)
- You will see that A8, A9, and A10 still repeat the value "ABC" (which is the purpose of the formula)
- Edit the formula of the cell below the emptied one (A8 in this case), just click in the formula bar, and press "Enter" without changing anything
- Force a workbook recalculation by pressing CTRL+ALT+SHIFT+F9
- Cell A8, A9 and A10 now will all carry a #N/A
This procedure allows to reproduce from scratch in a new workbook using Excel 1902.
However the criticality of this calculation bug is that when opening a workbook which was saved using 1808 (and make use of the formula described in step #2), while it worked fine with 1808, once opened using 1902 it throws errors.