Making "average" rows

[garybpt]

Hi everyone,

Hope you’re having brilliant weeks.

I’m trying to make a step challenge that I’ll be running for free. I’ve been able to split the results into teams but I’d like to create a league table based upon averages throughout the six weeks. I know how to do this in Excel but I can’t work out how to do it in Airtable. Does anyone have any ideas?

This particular base is on a free account so don’t have access to any blocks. My knowledge is pretty basic, too, so be gentle :joy:

Thanks,

Gary

[garybpt]

I’ve now moved this to a paid account so can act on any block recommendations :+1:t2:

[Justin_Barrett]

Airtable’s database-like design isn’t really meant to use individual rows for summaries or averages. This is in part because, unlike rows in a spreadsheet, database records have no knowledge of each other. This is part of the reason that you see summary bars in certain places, like at the tops of the groups you made, and also at the bottom of the main Airtable UI. Your groups already have created averages for those {Total} values, but if you end up with lots of teams, you won’t be able to see very many averages, even if you collapse the groups.

If you want to aggregate team averages in a way that would let you actually copy the values elsewhere, I suggest making a [Teams] table and changing your {Team} single-select field to a link field pointing to that table. The conversion will automatically create records in the [Teams] table for your current teams. On that [Teams] table, you can create a rollup field that will pull the values from the {Total} field on the [Challenge] table and average them, which will give you an average per team.

To do a similar think on a week by week basis, do a similar conversion for your {Week} field, turning into a link to a [Weeks] table. That will let you average all of the values for each week across all teams.

[garybpt]

Thanks, Justin, that’s been really helpful.

Following your advice I’ve managed to pull through the averages and totals into a [Teams] table so I can create my leaderboard and it seems to be working as it should.

I’m falling down on trying to bring each team’s weekly average into the [Teams] table so they can see what’s happening week by week. Could you advise?

Also, as there is no longer a dropdown which I can add team names to once they’ve registered. Is there a way to pull through all the different team names from the sign-up form into the [Challenge] table automatically?

[Justin_Barrett · Accepted solution]

garybpt:

I’m falling down on trying to bring each team’s weekly average into the [Teams] table so they can see what’s happening week by week. Could you advise?

You can use a lookup field for this. One option with lookup fields (and rollups) is to only collect data from records that meet certain conditions. This is activated via a switch on the lookup/rollup setup dialog.

Start by making a lookup that only pulls data from records where it finds “Week 1” in the field linking to the [Weeks] table. Once that’s set, duplicate it and change the condition to look for “Week 2”, and so on until you have six lookups.

garybpt:

Also, as there is no longer a dropdown which I can add team names to once they’ve registered. Is there a way to pull through all the different team names from the sign-up form into the [Challenge] table automatically?

If teams are signing up on the [Team Sign-up] table, you might consider using that instead of the [Teams] table. I’m guessing the primary field is the name of the team, so all you would need to do is add your rollups like before, and have the records in the [Challenge] table point there as well. That way you don’t need to copy team names anywhere, and teams are instantly set up for the challenge the moment they register. If you want to approve teams before they’re allowed in the challenge, you could add something like an {Approved Date} field, then make a view that only shows teams with a date in that field. Does that make sense?

[garybpt]

You’ve absolutely smashed it out of the park, Justin! Thank you very much! :slightly_smiling_face: