Formula in table doesn't calculate

[Jan Balje]

Hi,

I'm running into, for me, new and unexpected behaviour in Excel online.

When creating a sum formula at the end of a row, excel doesn't calculate the sum but instead shows the formula with a dashed line around it. Pressing the enter key or F2 does not result in the required calculation. 

I did check that "show formula's" is turned off. That function toggles other formula's in the sheet as expected.

I understand the notation with the @-sign, I don't think that has a relation with the problem.

Any ideas how I can get excel to perform the calculation?

Cheers,

JB

[#2]

@Jan Balje 

Try

=Table1[@verlofopbouw]+Table1[@meeruren]+Table1[@verlofopname]

[#3]

Thx for replying.
I created the formula by clicking on the cells. So Excel generated the notation with the semicolons (;).
When I remove the semicolons and give <enter>, Excel re-inserts the semi-colons and returns to the state as depicted in the original post.

Edit: it appears to be a problem with Excel online, because on Excel desktop app I cannot reproduce the problem. Unfortunately, security measures don't allow me to download the original excel file to my desktop.

[#4 · Accepted solution]

@Jan Balje 

That's weird. When I create such a formula in the Dutch version of Excel Online, it also inserts a semicolon - see 1.

But when I remove the semicolon, the formula works as intended - see 2.

 

[#5]

@Jan Balje By the way, it works correctly if I refer to the entire column:

=Table1[verlofopbouw]+Table1[meeruren]+Table1[verlofopname]

This formula will spill to the number of rows of Table1.

[#6]

I failed to get this working in the table, I kept getting #SPILL errors. I'm probably missing something.
In the end I just converted the table to a range and I could do what I want in no time at all.
It's Friday afternoon, after all.
Thx for the help.

[#7]

weird bug, and it still happens in 2024.

[#8]

@HansVogelaar  It happens to with with "downloaded" files. The solution in my case is change che cell type from "text" to "general".

After converting the cell Excel is accepting formulas

[#9]

Why a 2 years old bug is still open???