How do you calculate a percentage?

You could create a new table (“Percentage Calculation” etc) with a linked record field and rollup fields for the two columns you need to sum, then a formula field to calculate the percentage.

Then you can reference that formula field in the interface.

You can find more information about rollup fields here: https://support.airtable.com/docs/rollup-field-overview

There might be better solutions out there, but this can get you what you need.

This is doable in Airtable but it does take a few more steps.  First you are going to need to create a new formula field with {Column1} / {Column 2} to get your row level percentages.  Make sure your field settings are set to Percentage so that Airtable will know how to display it correctly.

If you want the grand total percentage across all rows you will need to create a separate table for that.  This is because Airtable is an Operational Database and is not like Sheets so it cannot calculate between rows(though you will see a non Row sum at the bottom of the table).  This table will have a single record in it.  Create two Rollup fields that connect back to your main table one with a sum of Column 1 and another for Column 2.  Create a final formula field with that calculates, “{Rollup Column1} / {Rollup Column 2} formatted as a percentage.  This field is also the field to reference a Number element in your Interface if you want to display your grand total percentage.

I hope this helps, let me know if you have any trouble setting up these fields?

​@May  You can always use the “Automations”... Could you please tell me more about how often the data changes and where you want to store the calculated percentage, etc.?

Hey ​@May,

As mentioned above you’d need to use a few workarounds to achieve this. What will you be doing with such value? If it’s just consumig it as a datapoint/insight but you do not need to actually do further work with it (e.g. send it via email etc), the easiest option would be to build a custom interface and ask omni to show a number element with this value!

Please let me know if this is not clear enough, I’d be happy to hop on a quick call and show you around. Feel free to grab a slot using this link.


Mike, Consultant @ Automatic Nation 
YouTube Channel

​@anthonyXRay has got the ticket for the simple solution to get what you need accomplished today, add a new formula field dividing field 1 by field 2, set to percentage. That gives you a per record percent. Then you can rollup into a new table or just change the quick value on the bottom to average instead of sum. That’ll average all your per record percentages into one figure and will adapt to whatever grouping/filtering changes you put on the table. I do just this for website analytics tracking, every record is a day, with data like OVR users and New Users, then I have a percentage formula for whatever statistics I want to calculate.

The other thing Anthony mentioned which you’re running into is how Airtable may look like a spreadsheet but functions very differently. It’s just close enough to Excel for people to approach it, but to truly use it you kind of have to reframe how you assemble things. I always say Airtable prefers to work horizontally (across columns and tables) instead of vertically.

Thanks everyone, I appreciate all your answers. I’ll try to do it the way you suggested.

We almost always create a table called Statistics.  Generally the table only needs one record (we use something simple as the name like a period (‘.’).  With automations, any new record created on any other table is automatically linked to this Stats table.  In the stats table you can create roll-ups for any of the other tables (e.g. sum of numbers) and then look it back up in the original table.  Then you can use formulas to create your percentages.   Simple example:

Table A

Stats Table

This can be used to perform many stats that are not native to Airtable (e.g. standard deviation, etc). 

Thank you, ​@Michael_Andrew , for your explanation.

Hey ​@May,

Sharing a few conlcusions from our call yesterday for any future reader:

1. Create a new table, link all correpsonding fields, rollup values, calculate the weighted average from the new table rather than on a record basis on your oriinal table.
2. Create custom interfaces using Omni -and iterating with Claude-. Ask Omni to get the data, process the data, and show a number element with the value. (note that you would not be able to use this data elsewhere, other than for “dasbhoard” pursposes, as it is not a value on your base but just an element on your interface)
3. Build something similar to the above, but using Zite instead to achieve scalability in terms of how many users get access to this interface.  
4. Final (which we did not disucss actually) have a new table, but run scheduled automations including scripts to the the calculations for you, and have the final outputs on a new table. In this way you can still show it on an interface, and you can also use it for other purposes.
    

Hope you enjoyed our discussion :D

Mike, Consultant @ Automatic Nation 
YouTube Channel