Trying to create an "averageif" in airtable

[cs]

Hello team! 

I am trying to calculate the average weekly attendance in airtable. I downloaded the data from airtable and was able to calculate it in excel using an "averageif" function but I am not sure how I can implement it in airtable. 

The last column is the average weekly attendance I want to populate in airtable and I was able to do it in excel with this function: =AVERAGEIF(D:D,D2,E:E)

The number of days a week we are open varies which is why we need a dynamic denominator to count how many days in a week we are open. 

Any and all help is greatly appreciated! Thanks so much 🙂

[TheTimeSavingCo]

Does this look about right to you?

You can duplicate the base here so that you can look at the formulas and stuff

The idea is to use a "Count" field to get the number of days you're open, use a rollup to sum the total attendance values, and use the "Unique Week Identifier" as the grouping.  We can automate this by creating an automation that'll paste the "Unique Week Identifier" value into the linked field too

[kuovonne]

Are you looking for a single average weekly attendance, or an average daily attendance per week?

If you want an average daily attendance for days open, Adam’s approach of using linked record fields and rollups is the way to go. However, you do not need three fields. You can do it all in one rollup field with the formula AVERAGE(values).

It isn’t clear to me what condition you want for which records to include, but you can include that condition in your rollup field. 

You can learn more about rollup fields here. 

[cs]

Hi Adam! 

Thanks for the help, this seems to be on track for what I am looking for! Is there a way to automate the dates that populate as linked records based on the unique week identifier? The entire data set has almost two years worth of data so it would be much easier if I didn't have to manually select the dates that are in a given week. Thank you again!

[TheTimeSavingCo]

Yeap there is, you can create an automation that'll trigger when the "Unique Week Identifier" field is not empty, and the "Weeks" field is empty, and it'll have an "Update Record" action that'll paste the "Unique Week Identifier" value into the "Weeks" field, and that'll work for all new records

For your existing records, you can just click the header of the field "Unique Week Identifier" (thereby selecting the whole column of data), clicking the header of the field "Weeks", and just pasting and that should just work!

Be sure to use @kuovonne's solution about using `AVERAGE()` too, it's a lot cleaner than mine.  Thanks kuovonne!

[cs]

Hi Kuovone,

So I have done that but I am unsure how to make it so the "where" statement encompasses whenever the unique identifier is the same. For instance the fifth week of 2023 will have the unique week identifier "20235" for each day the attraction is open and I need the average where the week identifier is the same, do you know how I could accomplish that? Thanks so much!