Forum Discussion

Europaul's avatar
Europaul
Copper Contributor
Apr 04, 2021
Solved

Slow updates

I have a spreadsheet with 3 tabs. I input data into 1 and it populates the other 2... Only 7 items get populated to one tab and 3 to the other, yet it takes over 30 secs for the 2 tabs to update - t...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 04, 2021

    Europaul 

    An issue is in formula in cell N134 of Logbook

    =SUMIF(A:A,">"&TODAY()-28*(E:E))

    Here for each cell in column A you use 1 million criteria (all cells in column E) to sum, thus formula makes 1M*1M = 1e12 calculations. After that it tries to return 1M results, but have not enough space for it and returns #SPILL error.

     

    In previous versions of Excel silent implicit intersection mechanism worked in the background,  formula calculated only for the criteria from current row.

     

    Not sure what exactly you'd like to calculate, simply disabled the formula. Please check attached file if it is updated correctly.

Europaul's avatar
Europaul
Copper Contributor
Apr 06, 2021
Excellent! Thank you Sergei (spasibo!) Yes, that (A:A, E:E) fixed the slow response...
Now I am curious... why did Nikolino report "For me, your file works without problems, quickly and does not hang anywhere." ???
Best regards,
Paul
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 06, 2021

Europaul 

Paul, you are welcome. Perhaps Nikolino is on Excel which doesn't support dynamic arrays (e.g. Excel 2019). If so mentioned silent implicit intersection works and it shall be no such issue. Such behavior helps sometimes, but in general could be source of other errors if use it incorrectly, plus dynamic arrays provide lot of new possibilities to build more reliable Excel projects in shorter time.

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Apr 07, 2021
    Now that you write it down, Mr. Baklan, I can see what's going on with the arrays, thank you ... You are right, at the moment it is only available if you take part in Microsoft's so-called Office Insider Program.

    I have to slowly decide on a direction ... subscription, or stay on 2013/2016/2019 and wait for 2022, or online?
    ... well, drink tea and wait :).
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 07, 2021

      NikolinoDE , dynamic arrays are in production for 365, shall be available on all channels. That's LAMBDA only for Insider Beta.

      One time purchased 2021 shall be available this fall, don't know which functionality will be included.

      • NikolinoDE's avatar
        NikolinoDE
        Platinum Contributor
        Apr 07, 2021
        It would be best if you could buy them all and use them all on one PC, depending on what you need.
        Until a clear path has crystallized, some time will pass.
        I will wait in advance for the 2021 and in the meantime experiment with online ... I can load that on the PC with my existing Excel.

        Interesting things will come our way ... it's nice to live in this time.

        Thanks for the summary - info 🙂

Resources