Multiplying by a percent column acts as if the percent were in fact an integer. So 100 * 50% yields 5000 in a formula column rather than the correct result of 50.
How does this only have 11 comments? This should have 1,111 comments. Airtable is such a wonderful product, but this one thing is so, so wrong. I honestly don’t know how a company that considers itself a database company can let a bug this big persist for years.
Please update/fix this. As discussed, the longer it goes the more formulas I have that include a *.01 calculation will need to be fixed. Just to be clear, it’s worth the short-term pain to fix as soon as you can.
Alternatively, if the delay is fear of breaking existing airtables, maybe you can just add a 2nd Field Type. Percent (as Integer) and Percent (as fraction)… or whatever word better describes the difference between the stored magnitude.
There are possible ways around this (e.g., a configurable option to enable/disable legacy functionality, or a versioning system that say 'this base created under Airtable #.#, so behave in such and such a manner"). We’ll have to see what Airtable chooses…
I suspect the horse has already bolted, so closing the door won’t help. Airtable has indicated there are so many customer formulas out there that assume ##% is equal to ##, rather than 0.##, correcting the problem would wreak havoc in existing applications. Instead, going forward there are steps one can take to minimize the issue.
One approach would be to wrap percentage values with the following (where {Pct} is the name of my percent field):
VALUE({Pct}&'%')
That will return the appropriate decimal value for the percent field. One good thing about this approach is that it will return the correct value regardless if {Pct} is a percent field or a text representation of a percentage — that is, using that function to wrap either 13% (percent field) or '13%' (text field) will return 0.13.
