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.

Anonym
2023-02-24T16:44:25+00:00

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.

Microsoft 365 og Office | Excel | Andet | Windows

Låst spørgsmål. Dette spørgsmål blev migreret fra Microsoft Support-community'et. Du kan stemme om, hvorvidt det er nyttigt, men du kan ikke tilføje kommentarer eller svar eller følge spørgsmålet.

0 kommentarer

11 svar

Sortér efter: Nyeste
Meget nyttig Nyeste Ældste
  1. Anonym
    2023-02-25T17:09:24+00:00

    Sure, let me explain the order in which Excel calculates formulas. When Excel calculates a workbook, it follows a specific order of operations to determine the value of each formula in the workbook. The order in which Excel calculates formulas is known as the calculation chain.

    The calculation chain starts with cells that contain input values or formulas that do not depend on other cells. Excel calculates these cells first and then moves on to cells that depend on these cells, and so on, until all cells in the workbook have been calculated.

    When you have a data table that depends on cells in another sheet, Excel needs to calculate the cells in the other sheet first, before it can calculate the data table. In Excel, there are different calculation modes that determine how Excel calculates the workbook. The "Automatic except data tables" calculation mode is designed to prevent Excel from recalculating data tables every time you change a value in the workbook. This means that Excel will not automatically recalculate the cells in the other sheet that the data table depends on, which can lead to the issue you are experiencing.

    In summary, when you have a data table that depends on cells in another sheet, and you are using the "Automatic except data tables" or "Manual" calculation mode, Excel may not recalculate the cells in the other sheet that the data table depends on, which can lead to incorrect results.

    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

    Var dette svar nyttigt?

    0 kommentarer
  2. Anonym
    2023-02-25T16:55:09+00:00

    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.

    Var dette svar nyttigt?

    1 person fandt dette svar nyttigt.
    0 kommentarer
  3. Anonym
    2023-02-25T16:34:41+00:00

    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?

    Var dette svar nyttigt?

    0 kommentarer
  4. Anonym
    2023-02-25T16:27:50+00:00

    Var dette svar nyttigt?

    0 kommentarer
  5. Anonym
    2023-02-24T20:44:54+00:00

    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

    Var dette svar nyttigt?

    0 kommentarer