This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Other questions
- Re: Calculation of Percent-Values

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

0
1442
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 17, 2017 07:36 AM

Hello, a very simple question …

How can i do an ordinary percent-calculation as follows (column “Percentage”):

Region +++ Sales +++ Percentage (favored result)

North +++ 40000 +++ 20 %

East +++ 70000 +++ 35 %

South +++ 30000 +++ 15 %

West +++ 60000 ++ + 30 %

++ Sum 200000 ++ Sum 100 %

How is the formula for the column “Percentage”? I tried it with “Sales/SUM(Sales)” in different ways, but the result is always 1.

Shame on me, but I have no idea anymore. Thanks for advice.

17 Replies 17

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 18, 2017 02:32 PM

You need the Total to do the math, and given that it is dynamic, you can’t do this. They only thing that comes to my mind is this:

What other tables or data do you have?

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 19, 2017 05:37 AM

This can be done by creating a sum roll-up field first, and referencing that instead of sum(sales)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 19, 2017 06:51 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 19, 2017 11:17 PM

The reason you are getting 1 as the result of sales/sum(sales) is because the numerator and denominator on that fraction are literally the same value. Sum() does not reference an entire column like =sum(A:A) would sum the A column in Excel. Using sum() in Airtable with a column name used as a variable, that column name will represent only the value of the field corresponding to the column name for the specific record in which the field calculated from that formula is held. Therefore, since sales is referenced within sum() as the only value, sum(sales) is the exact same 1-value array as the sales value that it is dividing. X/X=1 every time.

I believe that is a more complete explanation of why this is failing for you, OP.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 21, 2017 06:53 AM

Thanks for all response! I thought, a “SUM” without relation to another table simply totalize a column - like the “SUM”-Value at the bottom. Seemed obvious. Okay, I was off. It would be too easy … :winking_face:

Special thank to Elias for the (tricky) example, but unfortunately it can’t be applied to my tables.

For the moment I give up. Maybe I will try again later.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 26, 2017 01:03 PM

I THINK this is the right thread for this question - but if not, apologies and feel free to direct me elsehwere.

i’m WANTING to do a calculation of {Payment Due} x {Discount amt} but continuously get #Error in that column…

have tried changing discount amt to percentage field, number field, text field… and Payment Due is a single select… is that the problem? i need it to limit the options in that field…

would be grateful for any advice, even if the advice is “you can’t do that”… :slightly_smiling_face: then i’ll stop banging my head against it!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 27, 2017 02:44 AM

You want something like this? https://airtable.com/shr5ITe9SY7lorCEt

The formula is: `{Payment Due} - Discount`

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 30, 2017 05:58 PM

hi

nope. i thought it was right at first glance, but no, it’s not subtracting a % of the payment due…

it’s subtracting just the integer amount…

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 30, 2017 09:05 PM

Try this example base.

I think you ran into two problems. First, a single select value is returned as a string, which was the source of your `#ERROR`

messages. To use it in a calculation, first wrap it in a `VALUE()`

function.

But even if you had gotten past that hurdle, you likely would have slammed into Airtable’s buggy handling of percentage fields. When used in a formula, one would expect a percentage to have the value of its decimal equivalent – for instance, 10% = 0.1. Instead, an Airtable percentage takes on the integer value of the percent: 10% = 10. So, instead of

`VALUE({Payment Due})*Discount`

you need

`VALUE({Payment Due})*(Discount/100)`

Except in this case `{Discount}`

represents a percentage **off** `{Payment Due}`

, and not a percentage **of** `{Payment Due}`

. That means the formula you actually want is

`VALUE({Payment Due})*(1-(Discount/100))`

If I’ve completely misunderstood your intentions, feel free to run it over me again, and I’ll give it another try…

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 31, 2017 01:06 AM

holy smokes… i was nowhere close to finding that answer. Thank you!!

I had, actually tried variation like your first samples as those are logical and mathy…

I don’t think I’d have had the patience to learn that … I tried all my excel formulaic wizardry and finally gave up and just entered the actual discount amount manually (as generally speaking it’s a 10% discount it’s fairly easy to do in my wee head).

But your formula worked brilliantly, so, much appreciation for that!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 01, 2018 02:26 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 02, 2018 06:17 AM

For a trailing stop, from my understanding that is a binary check on whether a stock has moved a given percentage from an original point. For this you would need a comparison between two points such that IF they differ by a given percent or more, it will return a value of true. So the elements are calculating the difference and a check to see if the difference meets a threshold.

IF(ABS((Original-Current)/Original) >= .15, TRUE(), FALSE())

IF the Absolute Value of the Difference divided by the Original is Greater Than Or Equal To the Threshold, return True, otherwise return False. Here I have used 15% as the threshold.

I would include a link to an example but the forum won’t let me for some reason

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 02, 2018 06:36 PM

Thank you so much Thomas. I think I understand but since I am not as fluid in the terminology of a formula. I sill try and send you an attachment of one and then you can maybe give me the correct formula. That would be great :slightly_smiling_face:

I want to put a 30% stop on say each stock, and do it automatically for me every time a stock changes price. The formula would be what I Paid ($13.47) and say it goes up to $60 and starts going down when it reaches 30% more than what I paid, so I don’t lose money, I will put a 30% stop on that stock, so I know when to sell. So using $13.47 if it went up to $60, then I would see if I didn’t already at $17.51 theoretically. I think I explained it correctly. Thank you for your time, I appreciate that

**Patsy Pankey**

Email: digitalbarn@gmail.com

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 12, 2018 09:34 PM

it’s end of 2018, and this “feature” of handling percentage fields still exists

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 13, 2018 08:36 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 05, 2020 11:49 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 27, 2021 07:14 PM

Formula: cantidad*1 / {total}

Format: Percent (42%)

Precision: 1