Failed Test 1

Various adverse effects of 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
0
DispName
Quote for
Lead time
Price
1
samples:
$50.00
in 7 days
samples
7
$50.00
2
1500 pcs:
$21.45
in 40-45 days
1500 pcs
40-45
$21.45
3
3000 pcs:
$20.70
3000 pcs
$20.70
4
5000 pcs:
$20.35
5000 pcs
$20.35
5
samples: in 43864 days
samples
2-3
6
1500 pcs:
$24.34
in 30+ days
1500 pcs
30+
$24.34
7
samples:
$4.00
in 43894 days
samples
3-4
$4.00
8
1500 pcs:
$3.10
in 7 days
1500 pcs
7
$3.10
9
1500 pcs:
$11.80
in 7 days
1500 pcs
7
$11.80
10
3000 pcs:
$3.10
in 10 days
3000 pcs
10
$3.10
11
5000 pcs:
$3.10
in 15 days
5000 pcs
15
$3.10
12
samples:
$2.40
in 15 days
samples
15
$2.40
13
1500 pcs:
$2.40
in 15 days
1500 pcs
15
$2.40
14
3000 pcs:
$2.40
in 15 days
3000 pcs
15
$2.40
15
5000 pcs:
$2.30
in 15 days
5000 pcs
15
$2.30
16
samples:
$50.00
samples
$50.00
17
1500 pcs:
$27.82
in 30 days
1500 pcs
30
$27.82
18
1500 pcs:
$22.00
in 40 days
1500 pcs
40
$22.00
19
3000 pcs:
$20.76
3000 pcs
$20.76
20
5000 pcs:
$20.00
5000 pcs
$20.00
21
samples:
$50.00
in 44024 days
samples
7-12
$50.00
22
1500 pcs:
$18.12
in 30 days
1500 pcs
30
$18.12
23
3000 pcs:
$16.87
3000 pcs
$16.87
24
5000 pcs:
$15.34
5000 pcs
$15.34
25
samples:
$150.00
in 7 days
samples
7
$150.00
26
1500 pcs:
$13.93
in 40-45 days
1500 pcs
40-45
$13.93
27
samples:
$5.70
in 7 days
samples
7
$5.70
28
1500 pcs:
$5.70
in 7 days
1500 pcs
7
$5.70
29
3000 pcs:
$5.70
in 7 days
3000 pcs
7
$5.70
30
5000 pcs:
$5.70
in 7 days
5000 pcs
7
$5.70
31
1500 pcs:
$12.50
in 45 days
1500 pcs
45
$12.50
32
samples: in 20 days
samples
20
33
1500 pcs:
$27.00
in 20 days
1500 pcs
20
$27.00
34
3000 pcs:
$26.00
3000 pcs
$26.00
35
5000 pcs:
$24.50
5000 pcs
$24.50
36
samples:
$50.00
in 44022 days
samples
7-10
$50.00
37
1500 pcs:
$36.50
in 30-40 days
1500 pcs
30-40
$36.50
38
1500 pcs:
$37.27
in 30-40 days
1500 pcs
30-40
$37.27
39
samples:
$30.00
in 43897 days
samples
3-7
$30.00
40
1500 pcs:
$30.62
in 44129 days
1500 pcs
10-25
$30.62
41
3000 pcs:
$29.23
3000 pcs
$29.23
42
5000 pcs:
$27.84
5000 pcs
$27.84
43
samples:
$10.00
in 44022 days
samples
7-10
$10.00
44
1500 pcs:
$20.83
in 40 days
1500 pcs
40
$20.83
45
3000 pcs:
$20.49
3000 pcs
$20.49
46
5000 pcs:
$20.21
5000 pcs
$20.21
47
samples:
$30.00
in 44022 days
samples
7-10
$30.00
48
1500 pcs: in 35 days
1500 pcs
35
49
samples:
$200.00
in 20 days
samples
20
$200.00
50
1500 pcs:
$13.00
in 20 days
1500 pcs
20
$13.00
51
3000 pcs:
$13.00
in 20 days
3000 pcs
20
$13.00
52
5000 pcs:
$12.50
in 20 days
5000 pcs
20
$12.50
53
:
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.