Cell format is ignored in formulas

[MathildeMousset]

Scope: Setup + linelist

To Reproduce
I created a formula that was:

CASE_WHEN(
AND(vacci_yes = "Yes", vacci_doses = "1"), "1 dose",
AND(vacci_yes = "Yes", vacci_doses = "2"), "2 doses")

The important thing here is that the vacci_doses column is defined as text (takes values 1,2,3, 4 or more).

Expected behavior
The content of the column in the expected linelist is in text format, and thus should be treated as text in conditions (vacci_doses = "1" and not vacci_doses = 1).

Actual behaviour

• In the generated linelist, the vacci_dose column has the format "text" (can be seen either in the format field, or by the fact that the content of the cells is left aligned) => good
• The formula is parsed correctly => good

= SI(ESTVIDE($CA9);"";
SI(ET($CB9="Yes";$CD9="1");"1 dose";
SI(ET($CB9="Yes";$CD9="2");"2 doses";"")))

• The condition is not triggered. To make the condition work correctly, I needed to make the condition with naked numbers, as if they were numerics. => bad

= SI(ESTVIDE($CA9);"";
SI(ET($CB9="Yes";$CD9=1);"1 dose";
SI(ET($CB9="Yes";$CD9=2);"2 doses";"")))

I am afraid it's an Excel problem, because as far i can see your CASE_WHEN was translated properly in the linelist. But do you have an explanation or a fix or even a roundabout?

**Desktop:

• OS: Windows
• Excel version: 365

[yves-amevoin]

This is a trickier one, but I got a temptative of answer.

In excel, if I type in a numeric value, says 1 in cell A1 and then convert it to number, the test =(A1="1") in B1 returns FALSE. It is considered as a numeric value somehow.

On the other hand, if I convert A1 to text BEFORE entering the numeric value, then the test =(A1="1") returns TRUE.

Seems like the "type" of the input value in the cell is somehow kept fixed even if you change the formatting afterwards. So this might be related to the order in which formatting vs setting values is applied in the designer (which is a little bit tricky as changing the order might have other side effects I can not detect right now).

I have one more question to investigate: Is vacci_doses a dropdown?