Multiplying by percent column yields result that is 100 times too big [SOLVED]

I’m having the same issue. Any input from support on how to address this?

Apologies, our percent field behaves a bit differently than it does in excel, as you’ve pointed out. We treat the percent as formatting, and then just multiply the actual numbers. We’re looking into changing the behavior so that it does what you expect. In the meantime, you can divide everything by 100 as follows:

(Percent * Number) / 100

+1 for changing the behavior to match Excel.

A calculated field that returns 0.5 should be displayed as 50% when the format gets switched to a percentage.

Just noticed this too! When formatting a column where, for example, mathematical result is 1.03, formatting as a % displays as 1.03% in stead of 103%.

+1, this is really confusing.

No joke. +1 for making a percentage field type compute like… wait for it… a percentage.

PS- Sorry for the snark, but having a percentage field type that just adds a % symbol to an integer is just super funky. It makes sense to just add a % when you set the FORMATTING type to “percentage”, but not when setting the field type for a column…

This is still not fixed.

Percentages aren’t calculated as percentages, they are just “pretty to look at” with a % at the end.

+1 very confused by how Airtable multiplies percentages

This does seem to be one of the most common of Airtable 'gotcha!'s.

Until it’s fixed — and, unfortunately, the longer it goes unfixed, the greater the pressure will be on Airtable not to fix it, for fear of disrupting existing bases that have implemented workarounds — one can use the following function:

Instead of

Value * Percent

use

Value * (Percent/100)

Similarly, if calculating a percentage discount (as discussed in this post) you can use

Value * (1-(Percent/100))

to calculate the amount of {Value} after the percentage discount has been applied.

I have the same issue… somehow strange… unexpected that 20% x 1 give 20… ??

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.

Thanks,
Mike

Big fail here, peeps. Please correct.

Agree easy workaround. And think of all the bases that will have to have formulas change if they ever fix. But it is just not right.

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…

+1! I just spend 20 wasteful minutes trying to figure out what I was doing wrong before searching and finding this post.

Also this thread was started in January 2016. High time to fix it already!

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.

+1 on this. There’s no point having a percentage classification if it doesn’t operate as a percentage. Just confusing and a hidden source of error.

@Airtable do you read this thread?!

Hi all! Today, we’re releasing a fix to percent field behavior, something that many of you have requested:

• From now on, the values in all newly created percent fields will be treated as fractions of 100.
• All percent fields created prior to today have been converted into number fields with a toggle that says “Show percent sign.”

Context on this update

Due to decisions made very early on Airtable’s product development, it used to be that values in the percent field were not treated as fractions of 100, but rather as integer values with a percent sign. That means that if you had a percent field containing the value 50%, and you used this value in a formula, you would get an output that would be correct if you treated the value as an integer without the percent sign, but would be incorrect if you treated the value as a fraction of 100—for example, if you had a formula 1*{Percent} , it would return 50 (as if you were making the calculation 1*50) rather than .5 (as if you were making the calculation 1*(50/100)).

For many of you, this was not how you expected the percent field type would work. Any confusion or frustration you might have experienced as a result of this decision was completely justifiable—we’ve known for a long time that this was something that we needed to address.

However, Airtable is a complex horizontal product with many different features that can intersect with one another in myriad potential ways. Our product team needed to carefully consider all potential solutions and how they might affect our customers’ existing workflows. Here were just a few of our product team’s considerations:

• Formulas : many of our customers had implemented formulas with the existing behavior of the percent field in mind (e.g. by adding a /100 into a formula). Changing the behavior of the percent field could affect the values output by those formula fields.
• Filters : if customers had filters defined using criteria related to percent fields (or formula fields referencing percent fields), changing the behavior of the percent field could change which records are being filtered!
• View share links : related to filters—if customers have view share links that rely on filters, and which records are being filtered changes, changing the behavior of the percent field could potentially expose customer information that was previously hidden (!!!).
• API : changing the behavior of the percent field could also affect many of our users’ API integrations in ways that we cannot control.

With all of these considerations (and more) in mind, our product team developed a solution that will better align our product with customer expectations going forward, without disrupting any customers’ existing workflows. While newly created percent fields will behave as ratios, the mathematical values in all legacy percent fields (now number fields) will continue to behave as integers. With the solution we’ve implemented, you don’t have to take any action, and all of your API integrations, view share links, formulas, and so on, will continue to work as they did before.

If you are feeling so inclined, you can convert your legacy percent fields (which should now be number fields with a toggled-on “Show percent sign” option) into the new percent fields following these instructions.

Thank you all for your patience and understanding! :100: