BUG with big formulars in Excel _ displaying empty cells

Docs
1

Some complex formulas embedded in a excel document do not work in OnlyOffice.

We encountered an issue when working with large array formulas in our excel sheet. The array formula is supposed to take data updates from the next sheets.

When we open a spreadsheet (which works perfectly in MS Office) for the first time, the data based on calculation is there ( cells are filled with needed values), but with the first update of data on neighboring sheets , the array formula stops updating values anymore. The cells become empty, but when you click on them the formular is still there.

Copy/pasting the formulas again or typing them again from scratch don’t help either.

Version O.O - 8.3.0.94

2

Hello @NataliaS,
Please provide the original file with these formulas as well as the screen recording with the issue reproduction steps

3

Hello. i won’t let me upload any files as a new user. Can I send the files via email?

4

Now you have the permission to upload files here

5

6

Project1.xlsx (1.4 MB)

7

Thank you very much. I have uploaded the files.
OO is used on the server so multiple users can edit files. The issue is the same for one user or users who edit together.

Also tried the option : Formula tab > Calculation > Calculate a list. ( calculate a worbook doesn’t work all the times). But it is a workaround, still not convenient. Is there a permanent solution?

8

Hello. I was wondering if there was any update? Is there a permanent solution?
Or may it will be fixed with any new versions?

10

Hello, sorry for the delayed response. Trying to reproduce I changed S11, T11 cell values to 50000 and 400000 correspondingly in Forecast RU, then changed AI11, AJ11 cell values to 600 and 300. As the result, the values in NVP RUB_EUR sheet also changed accordingly. Did I miss some step?
Also, I’ve been using the latest 8.3.1 version during the test, please update to the latest version and check there as well

11

Steps in the Only Office file (1).docx (507.6 KB)
Hello. Tried reproducting the steps on the latest verision 8.3.1.25
Describted the steps performed and data entetred, also attached the screen shots, hope it will help more to shed the light.
The more we check the more formulars we find that miscalculate the results ( calclations differ if I open the same file in MS Office)

it’s really frustrating, as there is not garantee that calculations are correct in the end.

12

Hey @NataliaS

I followed your instructions from the document… yeah, even the painful ones. But, being the hero that I am, I managed to pull this off:

So, I ran the test, and guess what? Same results as MS Office. Not like that cursed screenshot where there’s an obvious difference… Weird, hein?! :face_with_raised_eyebrow:

Here’s what I found:

When it comes to formulas using =INDEX, things go horribly wrong. Maybe I missed a step, maybe the universe just hates me, but here’s my simple conclusion:

Different spreadsheet apps have different default regional settings:

  • MS Excel (English/US settings): Uses , (comma)
  • OnlyOffice, LibreOffice, and some regional Excel versions: Use ; (semicolon)

Which means the correct syntax depends on your spreadsheet software’s mood, I mean, regional settings:

  • Comma , separator (default in US/English Excel)
    =INDEX(C1:E3, 2, 3)
    Works fine in Excel (US settings), breaks elsewhere.
  • Semicolon ; separator (common in European settings, OnlyOffice, LibreOffice, Google Sheets, etc.)
    =INDEX(C1:E3; 2; 3)
    This works across all tested software.

Why This Happens?

  • The formula separator (; vs. ,) is defined by your OS or spreadsheet software’s regional settings.
  • If Excel also requires ;, that means your system’s regional settings are… special.

Solution:

If you want to force OnlyOffice to use , instead of ;, you can:

  1. Change your OS regional settings.
  2. Sacrifice a USB drive to the tech gods.

Or check this:

Screenshot_20250312_024606
Screenshot_20250312_0246061366Ă—724 58.8 KB

Side note:

  • If your decimal separator is a comma ,, your argument separator will be a semicolon ;.
  • If your decimal separator is a period ., your argument separator will be a comma ,.

Don’t ask me why. I don’t make the rules. :sweat_smile:

But wait… it gets worse.

I dig deeper, and just when I was about to give up, your cursed file put on its devil horns:

  1. Opened the file in OnlyOffice → BOOM formulas all broken, all showing #VALEUR! errors.
  2. Created a fresh, innocent, untouched sheet (yeah, even a gynecologist would approve).
  3. Pasted everything there → BAM, suddenly, everything works?!

New plan: Exorcism :fire:

  1. Copy everything to a fresh sheet.
  2. Delete the old, possessed one.
  3. Never speak of it again.

At this point, my instinct tells me the original file is corrupt. So, I took it for therapy with Dr. MS Office Online, and voilĂ :

Screenshot_20250312_021958
Screenshot_20250312_0219581366Ă—635 114 KB

:skull: The curse is real.

When I opened it in Excel Online, it detected corruption and “repaired” it by deleting/rebuilding some stuff. Maybe the OnlyOffice devs should consider adding a “hey, your file is possessed” feature. :sweat_smile:

What this means:

  1. “Plage nommée supprimée” → Some named ranges (custom cell references) were broken and got deleted. If your formulas depended on them, well… good luck.
  2. “Dessin réparé” → A chart, shape, or image was corrupted and got reconstructed or removed.

That explains why OnlyOffice (and every other app I tortured with this file) struggled. They were trying to read a broken file. Excel Online just confirmed it. That can slo explain why the calculations were wrong due to the corruption in some cells that are into the game…

If everything works fine now after the repair, you’re good! If not, you might need to manually recreate the deleted named ranges or check if any drawings/shapes went to the shadow realm.

Moral of the story:

If your spreadsheet starts acting possessed, throw it at Microsoft and let them “repair” it.

The real question:

Which cells did MS Office delete? Guess what? I have no clue.

I should also mention… I’m not an expert in this. I don’t even work with OnlyOffice. I’m just a guy trying to kill time. So yeah, there’s probably more to uncover here… maybe even bugs or dinosaurs lurking in that file.

Here’s the repaired file: (you may have to check all formulas or something, if it good enough, well… happy for you)
Project1.xlsx (1.4 MB)

Hope this helps (even just a tiny bit).

P.S. OnlyOffice seems to have moods. Sometimes, it shows “An error has occurred while opening the file.” Other times, it just opens it like nothing happened. Like, pick a struggle.

13

Thaaaaank you gazzillion for jumping through the hoops and testing out the possesed excel file !!!
Regional settings seemed to work and fixed most of the problems.

The formulars that won’t work are with INDEX. Trying to figure out which alternative funciton can be used instead.

Thanks again for helping !

14

Good evening. I’m trying to repeate these steps, but the file keeps failing on me. Most of the formulars quited working. I was wondering if you took any specific steps? Could you please share?

15

Hello. Could you please help to figure out why INDEX doesn’t want to work in the formular. I have attached the steps to reproduce earlier (attaching again) and the file I’m tring to work with

The problem is with the tab - NPV_RUB_EUR and cell C68
Steps in the Only Office file (1).docx (507.6 KB)

PDP_NPV _NPV_Test file 10.03.2025.xlsx (1.2 MB)

16

Hey @NataliaS

I’ll try to keep my answers short to avoid confusion, okay? :slightly_smiling_face:

  • All I did was copy a sheet and paste it into a new, empty XLSX file… (your file is big, and following the logic behind each cell’s formula was a nightmare ) that way the cells that were showing errors just work.
  • It was only then that I suspected corruption, so I took your original file for a little visit to MS Office Online… It’s been “fixed,” apparently. I attached it to my previous message:

Now, you’re asking the same thing but in a different way… I already explained why I suspect the =INDEX function isn’t working properly. I came to the conclusion that your software may be following different regional settings… Could you please check?!

Now about this:

Well, when I opened the file… it just worked! :face_with_raised_eyebrow: And the error shown in your .docx file isn’t there. I do believe you, no worries, but now I’m even more suspicious about regional settings or something. We’re using the same software version…

Here’s my screenshot—can you show me how the formula in C68 appears in the function bar on your end? I might be able to help you better.

Screenshot_20250316_234942
Screenshot_20250316_2349421366Ă—724 88 KB

  • Please note that I didn’t follow your instructions in the .docx file this time. I’ll investigate this in a few hours… (There may be a bug to report.)

I have change here the formula language into Russian language so that you not get lost:

Screenshot_20250317_001148
Screenshot_20250317_0011481366Ă—724 71.8 KB

17

NOW… I’m Officially Lost :thinking:

Following your instructions… I meticulously checked all the cells like a detective on a caffeine overdose… and guess what? All calculations match MS Excel Online and your Excel screenshot!

The infamous cell BS11 is highlighted (you mentioned it is red on your doc file)…

Screenshot_20250317_002658
Screenshot_20250317_0026581366Ă—724 63.3 KB

Screenshot_20250317_010324
Screenshot_20250317_0103241365Ă—631 72.4 KB

What you see in green says;
“REPAIRED WORKBOOK - We have temporarily repaired this workbook so that you can open it in read-only mode.”
Yeah… Another corrupted file! But now I have a real mystery on my hands—why does OnlyOffice handle corruption like a pro on my side but totally ghost you on yours? :thinking:

I even tried resetting OnlyOffice, sacrificed a few brain cells, and guess what? It still matches MS Excel’s results!

Also, about this:
“The values appear in row 06 only after recalculating the current sheet.” (Formula tab > Calculation > Calculate a sheet).
Yeah… nope. I didn’t need to recalculate. Just to be sure, I manually recalculated like a good little test subject, and guess what? Nothing changed.

So, my final genius move:

I officially request that you exorcise your OnlyOffice installation and reinstall it from scratch.
(I know, it might not be the magical fix, but hey, let’s pretend it is for now.)

Here the “Repaired file” Now you just need to light some candles and chant: “Oh mighty spreadsheet gods, bless us with error-free formulas and alignment with Excel’s divine calculations!”.
PDP_NPV _NPV_Test file 10.03.2025(1).xlsx (1.2 MB)

If OnlyOffice keeps acting up, we might have to perform a ritual sacrifice… maybe offer it a fresh .xlsx file and some RAM as tribute.

UPDATE: @DmitriiV correct me if I’m wrong here:

Since you’re running OnlyOffice inside a container wrapped in Directum RX sorcery, there’s a solid chance that the container is starving it of resources or messing with calculations. Meanwhile, over on my end… running it normally… everything matches Excel perfectly.

You might want to summon the system administrator and ask if there are any hidden restrictions or settings causing this discrepancy.

Now, let’s play the Blame Game “TM” :

  1. OnlyOffice – If it fails at math in certain conditions, it’s guilty. But since it works fine outside the container, we might give it a pass (for now).
  2. Directum RX Dev Team – If their integration is throttling OnlyOffice’s brainpower (via resource caps, weird configs, or middleware shenanigans), then yeah, they’re holding the knife.
  3. Container Environment – If it’s treating OnlyOffice like a prisoner in a low-power jail cell, then the real villain is the setup itself. (If the container has restricted CPU/memory access, recalculations might not work properly.)

So, it’s not just an OnlyOffice meltdown… it’s OnlyOffice trying to survive in a hostile environment… and Directum RX might be making it worse.

Best move? Call in the IT squad. Maybe all they need is to flip a config switch instead of performing a full-blown system exorcism. :laughing:

1 Like
18

Hello @NataliaS, during the tests on my side all calculations matched those within MS Excel as well. As for the corrupted file warning, I didn’t see it within the editor when I was opening it.
Please clarify, do you use Power Query or Power Pivot when creating the file? We encountered the warning “External data connections have been disabled” when opening the file