Help

Formula not working / just creates an error

Topic Labels: Formulas
Solved
Jump to Solution
2609 28
cancel
Showing results for 
Search instead for 
Did you mean: 
Jason_Nash
6 - Interface Innovator
6 - Interface Innovator

{Business User Value} + {Time Criticality} +{Risk Reduction - Opportunity Enablement} / {Story Point Estimate}

The first part works great then I add the / and error
its nothing to do with the format of the fields as {Story Point Estimate} + {Business User Value} works fine… any ideas? its seem really odd to me

28 Replies 28
Jason_Nash
6 - Interface Innovator
6 - Interface Innovator

image

Formula now creates an #ERROR! only difference / rather then +
({Business User Value} + {Time Criticality} + {Risk Reduction - Opportunity Enablement}) / {Story Point Estimate}

Try naming your fields with words with words… I have dont that with mine and it does not work I will do again now and send over for you to see

Jason_Nash
6 - Interface Innovator
6 - Interface Innovator

@Jeremy_Oglesby THANKYOU odd if I put VALUE on everything it did not work but if I do it just one the / value like this finally works so very inconsistent behaviours going on but very grateful for a working scrore

image

For future-proofing and ensuring proper functionality in all cases, I’d recommend wrapping ALL of your field names that reference Single-Select fields with which you want to do math operations in the VALUE() function (including the other field names in the formula you just posted), to make the conversion from string to integer explicit. 6 months from now when something breaks and you go back to look at this and try to figure out what the heck is going on, you’ll be glad to have your formulas be as explicit as possible – don’t trust yourself to remember that all the other values are being implicitly converted, while the one divisor is being explicitly converted.

Just to reiterate, this is not a bug. You are adding/dividing several strings that look like numbers, but are not actually numbers. This is why you need to use VALUE(). Math operators are intended to work for numbers and not strings, issues that arise from feeding a math-based formula strings instead of numbers are to be expected.

Jason_Nash
6 - Interface Innovator
6 - Interface Innovator

Strong typing all the way… but would help if the formula documentation was more explicit about this. But thanks again… its also very inconsistent it works with + - but not with * /

Its very inconsistent behavior an why if thats the case would + work - work - they should not work gif your point was accurate they should all error if they are strings and Integers you cant add strings unless its doing some kind of type conversion in the background and it that case its not doing it for * and /
That level of inconsistency is a poor user experience even if you don’t call it a bug… But thank you for all your help and @Jeremy_Oglesby also

Generally speaking, in programming it is a “safer bet” to add number-adjacent strings than it is to divide (or multiply) them. Essentially, when you use two (or more) string values and try to perform math on them you are forcing Airtable to guess. There is a complicated answer here relating to how Javascript works under the hood but to cut a long story short: the more data you put in the less reliable you likely would have seen even if you used straight addition and no division. You are supposed to turn strings into numbers before performing math on them, whether its addition, subtraction, multiplication, or division.