Weird Simple Excel Formula Behaviour

Ask for Help
1

Hi everyone!

Just making some workaround for a problem. I tried Excel Formula Column and still can’t figure out why this happens:

  • Column 1 is a boolean.
  • Column 2 is a Template that only shows the Column1 value.
  • Column 3 is an Excel Column with the easiest formula in the world and the outcome is completely wrong.

See the attached screenshot and please let me know where is my mistake!

Thank You!

image
image1405×421 33.2 KB

PS: If I change to formula like in picture 2, everything works just fine

image
image1645×424 29.7 KB

1 Like
2

Regardless if your Excel formula is correct or not, you could use native computed columns in Glide Tables instead of the Excel Formula computed column.

Can you not use a template or if-then-else computed column to get the output you need?

3

Yeah, you’re right, I usually use that combination but while trying new things I came across this unexpected result. The formula is easy: IF(A1 = true, “A1 is true”, “A1 is false”) which should return the message “A1 is true” for checked rows and “A1 is false” for unchecked rows, but Glide does exactly the opposite in here and I just can’t understand why that happens…

4

Hm. I would consider the following:

  1. Does your Excel formula work correctly when you target the column “Boolean Value”? If so, then that could be an indication that the “glitch” if we can call it that might have to do with the nature of the boolean column in Glide.
  2. A boolean column in Glide is a checkbox, and when checking for conditions on a checkbox usually the options are “is checked” or “is not checked”. So sure, this to us is close enough to “true” and “false”, but really this is not the same, so something might be happening under the hood that might explain the glitch. Also, the fact that your formula happened to work in one case does not mean that your formula is indeed correct.
  3. Let’s remember that in Glide, a boolean value has 3 states : not checked (initiation), true, false (the state after true). So there’s a subtlety there that perhaps is getting overlooked by your formula.

Interesting though, thanks for sharing. Another good reason to not use the Excel formula computed column :slight_smile:

Another advantage of sticking to Glide computed columns is that one might get the logic wrong, but one cannot get the syntax wrong because there is none. With an Excel formula (or code in general I guess), one can get logic and syntax wrong, thus increasing error surface area.

5

Hola!

To get correct results, use capitalized words in formula, see my screenshot as help.

image
image1015×478 26.3 KB

6

OMG…

I swear I tried both!! But you’re totally right!

Problem solved!

Thank you very much, guys!

PS: @nathanaelb I agree we should not use non-native functions but in this case, Excel can save me from creating more than 400 native computed columns, that’s why I was messing around with that…

2 Likes