Calculation of Percent-Values

[Der_Copytexter]

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.

[Elias_Gomez_Sai]

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?

[Thomas_Jaccino]

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

[Elias_Gomez_Sai]

Thomas_Jaccino:

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

Yes, but you need first need to have all the numbers in the same records, that is what I’ve done. I don’t understand you reply.

[Thomas_Jaccino]

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.

[Der_Copytexter]

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.

[kera_mchugh]

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!

[Elias_Gomez_Sai]

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

The formula is: {Payment Due} - Discount

[kera_mchugh]

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…

[W_Vann_Hall]

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…