Skip to main content
Solved

Weirdest problem on "superior to" formula

  • September 23, 2021
  • 4 replies
  • 33 views
O

Hi all,
I’ve been spending around 2 hours trying to understand what I was missing in a simple “if X superior to Y, then show X, otherwise show Y”…
Here’s a screencast of what’s happening, can’t believe my eyes :Screencastify
I have Y = 11.8
If I change X to 9, the formula says X is higher than 9…
Very weird behaviour here as shown above.
Any hint on this amazing bug??

Best answer by oceanseleven11

Hi all, thanks a lot for your answers!
Did manage to transform the lookup value by adding a &’’ at the end of the string, don’t know what it really changed but it works now - didn’t work when I was just using the value formula.

VALUE({NumberIWant}&’’)

Thanks for your help!

4 replies

A
Forum|alt.badge.img+18
  • augmented
  • Inspiring
  • September 23, 2021

Hi Jules. There’s no problem with Airtable. You are comparing two strings. You need to change them to numeric type if you want to consider them non-lexically. You can use the VALUE() function for that.

Also, it’s generally not a good idea to mix numbers and strings in the same column. Hope this helps you.

Alexey_Gusev
Forum|alt.badge.img+25
  • Alexey_Gusev
  • Brainy
  • September 24, 2021

I would like to add more details. At first, change your formula to VALUE() of first field, then - for second, just to ensure it properly converted to number (number will align to right cell side, not left).
You second field is a lookup result, which sometimes may behave unexpectedly and needs additional efforts for convert.

O
  • oceanseleven11
  • Author
  • Inspiring
  • Answer
  • September 24, 2021

Hi all, thanks a lot for your answers!
Did manage to transform the lookup value by adding a &’’ at the end of the string, don’t know what it really changed but it works now - didn’t work when I was just using the value formula.

VALUE({NumberIWant}&’’)

Thanks for your help!

Alexey_Gusev
Forum|alt.badge.img+25
  • Alexey_Gusev
  • Brainy
  • September 24, 2021

Hi all, thanks a lot for your answers!
Did manage to transform the lookup value by adding a &’’ at the end of the string, don’t know what it really changed but it works now - didn’t work when I was just using the value formula.

VALUE({NumberIWant}&’’)

Thanks for your help!


Just to clarify.
As i said, lookup is not usual field, it supposed to be array of multiple values. Even if if you have just one link in linked field, lookup is the array from single value.
{NumberIWant}&’’ is a short form of CONCATENATE({NumberIWant},’’), in such way you convert array to string. VALUE(string) is ok, while VALUE(array) is not. That’s why it works.

Terms & ConditionsAccessibility statement