Calculating Average for multiple columns across tables

[Victoria_Lewin]

Hi everyone,

I have a question about calculating averages. I’m not exactly sure if it’s currently possible to do this with Airtable, and I have been searching for a while, so if anyone knows how I can do this, I would appreciate the help.

So I have attached an image of an example of the kind of base we are using. My organization will be using this for user acceptance testing, and each base will consist of a table for each of the testers. Each table is essentially identical. They all have the same columns, and everything up to the breaker/divider (Product Area, Acceptance Requirement, and Instructions) will have identical records as well across tables. Each tester has their own table, and fills in the fields after the divider (Status onwards).

Basically, we would like to be able to calculate the average status, usability rating, and Time on task for each of the items. Meaning that, we want to be able to take each of the tester’s Time on task record entries, and calculate the average for them. However, since each of the testers are entering their time, usability rating, and status in separate tables, I have yet to figure out if this is possible.

For example, for item number 1 (the first record in the table), we would like be able to find out how long it took the testers on average to complete that specific item. We would like to do the same for the usability rating and status, so in all of these cases I would need to be able to pull data from all of these tables into one record so that we can see the average across users (and therefore across tables).

I am pretty lost trying to figure this out, so any help would be much appreciated.
Thanks!

[Luiggi_Cuozzo]

Hi there

A couple of questions:

1. All the testers have the same tasks (records). I mean record #1 will be the same for everybody ans so on?
2. Do you averages be by record or by group of records (e.g. “Area 1 group”)?

Rgds

[Victoria_Lewin]

Hey, thanks for responding.

1. All testers have the exact same tasks. Record number #1 is identical for everyone, same columns/column headers. They just have to fill in the Status, Actual Result, Usability Rating, and Time on task based on their testing results. So the table you are seeing in the screenshot is exactly the same for all the tables, except for the responses they put into status/actual result/usability/time on task obviously.

2. I would like to calculate the average based on a group of records I guess. Not sure if I know exactly what you mean. So lets take the first record (which is the same in ALL tables) for example. Each tester will go to Area 1, and test using the acceptance requirement and instructions in that same record. Then they’ll enter their Time on task, so in this case it took them 5 minutes (looking at record #1). So I want to take the Time on task from each of the testers, so from UAT Tester 1, UAT Tester 2, UAT Tester 3, UAT Tester 4, and UAT Tester 5, and calculate the average time it took for them to complete record #1 (since record #1 is identical for all of their tables).

Since I will have many records, and many tables to work with, I’d like to know if there’s a way to set up formulas for this to function correctly.

Thanks.

[Luiggi_Cuozzo]

OK, taking into account your sample and my understanding I tested this:

1. I created 3 similar tester tables (just for the test, could be any number of them)
2. I reorganized your first field in order to have a unique identifier for each record (now is a formula which generates a unique ID)
3. I created a Calc table where I have all tester times for each record and the corresponding average for each one.

If this work for you I’ll share my test base.

PS: Don’t take into account the other tables (they are from other tests)

Rgds

[Victoria_Lewin]

Hi there,

Yes, this is perfect, Exactly what I was trying to figure out how to set up for a few of my calculations, but every time it wasn’t coming out right.
If you can share your table with me that would be great :slightly_smiling_face:

Thanks for your help.

[Luiggi_Cuozzo]

Sent you a DM for table sharing.
Rgds

[Mohamed_Moustaf]

Hi Luiggi_Cuozzo,

I’ve the exact same problem here, can you please send me the test tables?

Thanks a lot.