Skip to content
Gallery
Failed Tests
Failed Test 1
Failed Test 2
Test 3
Text Input
Cell to Crash
Cell Edit Bugs
Link URL not searched
Share
Explore

Failed Test 1

Various adverse effects of implicit column type auto-detection
DS
Dmitri Silaev
SYNOPSIS:
Problems with implicit column type auto-detection
⁠
⁠
Steps to reproduce:
Copy this doc to your own workspace
The below table is completely set up to showcase the errors
First, consider that the column type of Lead time is Text:
⁠
image.png
⁠
⁠
Effect 1:
Right click the DispName header cell and select Edit formula
You should see the following:
⁠
image.png
⁠
⁠
Note that the Lead time column type shown here is “123” which means Number. Not Text like we’ve seen before
Close the formula edit dialog
Effect 2:
Consider the following rows:
⁠
image.png
⁠
⁠
⁠
image.png
⁠
⁠
These are showing DispName values calculated fine.
Now consider rows:
⁠
image.png
⁠
⁠
⁠
image.png
⁠
⁠
These are displaying incorrect values in DispName.
Seems that “2-3” and “7-12” are trying to treat themselves as formulas or kind of. At the same time, “40-45” from row #2 is not causing this error. I tried to change the first number in “2-3” all way up to 12 and the calculation breaks. Not afterwards.
Workaround:
It seems, the Lead time column type is auto detected based on the percentage of “number-like” values.
Changing the first/last cell value did not help.
Adding a dummy row with non-numeric values did not help.
Copying values to a fresh column and adjusting the formula did not help.
Different manipulations with pasting from clipboard did not help.
Adding a space or apostrophe to all cells did not help - it swallows that and keeps breaking.
The only way to fight it at scale I found so far is prepending all cells with a character like ` not tracing back to the Excel legacy. But that’s not looking good.
Thanks for consideration!
Column type auto-detect failure test
DispName
Quote for
Lead time
Price
⁠
samples
⁠
: $50.00in 7 days
7
$50.00
⁠
1500 pcs
⁠
: $21.45in 40-45 days
40-45
$21.45
⁠
3000 pcs
⁠
: $20.70
​
$20.70
⁠
5000 pcs
⁠
: $20.35
​
$20.35
⁠
samples
⁠
: in 43864 days
2-3
​
⁠
1500 pcs
⁠
: $24.34in 30+ days
30+
$24.34
⁠
samples
⁠
: $4.00in 43894 days
3-4
$4.00
⁠
1500 pcs
⁠
: $3.10in 7 days
7
$3.10
⁠
1500 pcs
⁠
: $11.80in 7 days
7
$11.80
⁠
3000 pcs
⁠
: $3.10in 10 days
10
$3.10
⁠
5000 pcs
⁠
: $3.10in 15 days
15
$3.10
⁠
samples
⁠
: $2.40in 15 days
15
$2.40
⁠
1500 pcs
⁠
: $2.40in 15 days
15
$2.40
⁠
3000 pcs
⁠
: $2.40in 15 days
15
$2.40
⁠
5000 pcs
⁠
: $2.30in 15 days
15
$2.30
⁠
samples
⁠
: $50.00
​
$50.00
⁠
1500 pcs
⁠
: $27.82in 30 days
30
$27.82
⁠
1500 pcs
⁠
: $22.00in 40 days
40
$22.00
⁠
3000 pcs
⁠
: $20.76
​
$20.76
⁠
5000 pcs
⁠
: $20.00
​
$20.00
⁠
samples
⁠
: $50.00in 44024 days
7-12
$50.00
⁠
1500 pcs
⁠
: $18.12in 30 days
30
$18.12
⁠
3000 pcs
⁠
: $16.87
​
$16.87
⁠
5000 pcs
⁠
: $15.34
​
$15.34
⁠
samples
⁠
: $150.00in 7 days
7
$150.00
⁠
1500 pcs
⁠
: $13.93in 40-45 days
40-45
$13.93
⁠
samples
⁠
: $5.70in 7 days
7
$5.70
⁠
1500 pcs
⁠
: $5.70in 7 days
7
$5.70
⁠
3000 pcs
⁠
: $5.70in 7 days
7
$5.70
⁠
5000 pcs
⁠
: $5.70in 7 days
7
$5.70
⁠
1500 pcs
⁠
: $12.50in 45 days
45
$12.50
⁠
samples
⁠
: in 20 days
20
​
⁠
1500 pcs
⁠
: $27.00in 20 days
20
$27.00
⁠
3000 pcs
⁠
: $26.00
​
$26.00
⁠
5000 pcs
⁠
: $24.50
​
$24.50
⁠
samples
⁠
: $50.00in 44022 days
7-10
$50.00
⁠
1500 pcs
⁠
: $36.50in 30-40 days
30-40
$36.50
⁠
1500 pcs
⁠
: $37.27in 30-40 days
30-40
$37.27
⁠
samples
⁠
: $30.00in 43897 days
3-7
$30.00
⁠
1500 pcs
⁠
: $30.62in 44129 days
10-25
$30.62
⁠
3000 pcs
⁠
: $29.23
​
$29.23
⁠
5000 pcs
⁠
: $27.84
​
$27.84
⁠
samples
⁠
: $10.00in 44022 days
7-10
$10.00
⁠
1500 pcs
⁠
: $20.83in 40 days
40
$20.83
⁠
3000 pcs
⁠
: $20.49
​
$20.49
⁠
5000 pcs
⁠
: $20.21
​
$20.21
⁠
samples
⁠
: $30.00in 44022 days
7-10
$30.00
⁠
1500 pcs
⁠
: in 35 days
35
​
⁠
samples
⁠
: $200.00in 20 days
20
$200.00
⁠
1500 pcs
⁠
: $13.00in 20 days
20
$13.00
⁠
3000 pcs
⁠
: $13.00in 20 days
20
$13.00
⁠
5000 pcs
⁠
: $12.50in 20 days
20
$12.50
:
​
​
There are no rows in this table
⁠

Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.