Help

Weirdest problem on "superior to" formula

Topic Labels: Formulas
Solved
Jump to Solution
656 4
cancel
Showing results for 
Search instead for 
Did you mean: 
oceanseleven11
5 - Automation Enthusiast
5 - Automation Enthusiast

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??

1 Solution

Accepted Solutions
oceanseleven11
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

See Solution in Thread

4 Replies 4
augmented
10 - Mercury
10 - Mercury

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.

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.

oceanseleven11
5 - Automation Enthusiast
5 - Automation Enthusiast

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.