Excel 'What if' Data table cannot simulate using data from a different sheet in Windows 11. Behavior works in Windows 10, same version of Excel.

Question

We are operating a large excel sheet, running Monte Carlo simulations (10.000's) using data tables, drawing on data from another sheet where multiple values are dependent on the Trial ID. We have recently experienced an issue where behavior for users have changed when upgrading their Windows version from Windows 10 (business) to Windows 11.

Data Tables cannot perform simulation using data from a different sheet (in same workbook) when using "Calculate Sheet" ("Automatic, except data tables" enabled). Previously, this have not been a problem.

The issue is reproducible in a clean workbook!

Sheet 1 has data, which changes depending on a trial ID. Sheet 2 contains trial ID, and a column of numbers [1..200]. The goal is to calculate a sum across three rows in Sheet 1 - Subtotal(9;C2:C6), and display this sum for each of the 200 Trial IDs using a data table.

Set-up:

1. Create workbook with two sheets.
2. In Sheet 1, in row 1, fill in A1( =Sheet2!$B$2 )
3. In Sheet 1, in row 3, fill in A3("Column 1") B3("Column 2") C3("Column3").
4. In Sheet 1, in row 4, add these formulas: A4( =$A$1+3 ) B4( =A4*2) C4( =SUM(A4:B4)
5. Fill formulas from row 4 to rows 5 and 6.
6. In Sheet 2, in row 2, fill in A2("Trial ID:") B2( "1" )
7. In Sheet 2, in row 4, fill in C4( =SUBTOTAL(9,Sheet1!C4:C6)
8. In Sheet 2, in column B, Fill in numbers 1..200 starting in cell B5.

Intended behavior in Windows 10, 22H2 with excel v 2301 build 16026.20200:

• When you set up a data table with the 200 trial ID's and click "Calculate Sheet" (with feature "Automatic, except data tables" enabled), Each trial is run, and produces different values, depending on the data in Sheet 1.
• If you change data in Sheet 1, return to Sheet 2, and click "Calculate Sheet", each trial is re-run with updated values.

Wrong behavior in Windows 11, 22H2 with excel v 2301 build 16026.20200:

• When you set up a data table with the 200 trial ID's and click "Calculate Sheet" (with feature "Automatic, except data tables" enabled), the first run may produce all 0's for each trial. Following attempts will provide the same value for all trials.
• If you now run "Calculate Now" (to calculate entire workbook), you get correct results as per Windows 10. However, if you now change a value in Sheet 1, go back to Sheet 2, and run "Calculate Sheet", it will again provide the same value for all trials.

We have reproduced good behavior on several Win 10 devices.

We have reproduced bad behavior on several Win 11 devices, leading us to believe the change is related to OS, and not excel version.

Can anyone else reproduce this behavior in Windows 11? Is there a setting we need to change?

This is a major issue for our workflows, as using "Calculate Now" in stead of "Calculate Sheet" means we have to run all simulations (24 data tables á 10.000 trials), rather than selected data tables, leading to each simulation run taking 15-30 minutes rather than seconds.

Theory: My theory is, that some behavior has changed from Windows 10 to Windows 11, where "Calculate Sheet" in excel is no longer allowed to access/calculate dependent fields in other sheets, as part of its calculation, leading to no values changing with trial ID. Why this change happens, I do not know.

Answer 1

Jeg har Windows 11, version 22H2, OS build 22621.1265. Jeg kan ikke genkende det du skriver her:

Wrong behavior in Windows 11, 22H2 with excel v 2301 build 16026.20200:

• When you set up a data table with the 200 trial ID's and click "Calculate Sheet" (with feature "Automatic, except data tables" enabled), the first run may produce all 0's for each trial. Following attempts will provide the same value for all trials.

Answer 2

Sneha,

Thank you for your response.

Yes, it is likely Windows 11-related issue. My colleagues using a Windows 10 device with same version of Excel is not seeing this issue, and I have only started seeing it after updating to Win 11.

I understand the part with Named ranges. We have that in our original document with same behavior, so this does not solve the issue. The issue does not exist when "Automatic" calculation is enabled. Only when "Automatic except data tables" or "Manual" is enabled. The issue is, that we cannot run our main sheet with "Automatic", given it would attempt to resimulate 240.000 trials each time you change one value.

For the order in which Excel calculates, can you elaborate on what you mean?

Answer 3

SimulationErrorTest.xlsx

Video of reproduction:

https://acias-my.sharepoint.com/:v:/g/personal/msp_aci_dk/EcyIMDFMvKFAqE4_BBAPuB8BpTVar_tK4QealG_UwYH4Fw?e=WFzxlH

Answer 4

Hi MrSpence,

I'm Sneha and I'd be happy to help you out with your question. Sorry for the inconvenience caused.

Based on the information provided by you, it is possible that the issue you are experiencing is related to changes in the operating system rather than Excel itself. It is possible that Windows 11 may have made changes to how Excel interacts with other parts of the system or New update is having some compatibilities issues with Versions of Excel and Windows 11 you're using please try to Update Excel to latest version, which could be affecting the calculation of data tables that rely on data from other sheets.

To test this theory, you could try running the same simulation on a Windows 11 device using an earlier version of Excel (if available), or on a Windows 10 device using the same version of Excel that you are using on the Windows 11 device. This could help to determine whether the issue is related to the operating system or Excel itself.

Alternatively, you could try modifying the setup of your data tables to see if this resolves the issue. For example, you could try using a different method to reference the data from Sheet 1 in your data table, such as using named ranges or defining the data range explicitly. You could also try using different calculation settings in Excel, such as changing the calculation mode from "Automatic" to "Manual" or changing the order in which Excel calculates formulas.

If you have any other questions or need assistance with anything, please don't hesitate to let me know. I'm here to help to the best of my ability.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Best Regards, Sneha

Answer 5

Har du mulighed for at uploade en kopi af din "clean workbook" to Dropbox og så lægge et link til filen herinde?

Jeg har ikke meget lyst til selv at sidde og konstruere dit Set-up.