Get an average of scores from Airtable

Hello everyone.

I need the average of the last column, but the average is for the same LOCAL, same DATE and same TURN.
For each of this 3 points I want an average.
Tks!!

I

You can use an average formula in Airtable or use the math blocks in Thunkable.

I don’t understand what you mean by “is for the same _____.” Can you give an example? How would you find the average for one row if you weren’t using Thunkable?

Is a surf app. The surfer will rating a surf day.
Imagine that I surf in “A” beach (LOCAL), yesterday (DATE), in the morning (TURNO (am/pm)) and I rate the surf/waves with a 8 score.
You surf on the same LOCAL, DATE and TURNO and give a 6 score.
Then, for the same LOCAL, DATE and TURNO the score average is 7.
If you surf today this score don’t enter on average of yesterday, it’s another average.
This is the order of importance:

— LOCAL
— DATE
— TURNO
— NOTA (score)

— LOCAL
—------- DATE
—-----------------TURNO
—-------------------------- NOTA (score)

Im using Thunkable.
This data is from Thunkable.
The people rating there, and the data (ID, LOCAL, DATE, TURN, SCROE) goes to Airtable, like the table that I posted.

Okay, I understand now. You can either loop through the dates in Thunkable and sum the scores for each date that is the same or you can use a custom view/filter in Airtable to retrieve only the scores for a specific day. I think I might try to do all of this in Airtable and have the spreadsheet calculate the averages for each day. And then pull the values into Thunkable.

The main problem with that approach is that you can’t use a variable to dynamically reference an Airtable view. So I’m not sure how to connect each new day with Thunkable. Maybe it all has to happen in a single view. Probably.

But I need to use this dynamically. For each place will have a different average. I will use it to TRY to predict the next wave score.
I want to compare the average with some surf forecast data (windy, swell…).
Of course that I don’t know if its possible.

It sounds like you need to figure out the algorithm for all of these averages and predictions. Or maybe you already know it. But you’ll need to explain it in lots of detail so that someone here can help you figure out the blocks you will need in Thunkable.

1 Like

First of all, you need another column in your table to keep that average NOTE.
You just can’t replace NOTE with average, as a user can further give a NOTE and average will be re-calculate after that.
This being said, lets think further.
I think about this algorithm this way: average will be given by sum of all notes of records that matches condition / number of those records.
So you need to loop through all record, check if matches condition, sum the notes and divide to its number.

It might be some further speed or optimized improvements for this, I just want it to give you some fast guidance on how to think for your problem.

Notice This is for a local table. As I don’t use Airtable i don’t know if there is some automation that will help you skip some of steps from here.

2 Likes

Tks!!
But… How I find this blocks:

  • number of rows in…
  • update value in…

If you add as data source your Airtable table,


you will get as blocks those:

2 Likes

Thanks! Lets do it!

Ok. Let me know if you succeeded :slight_smile:

1 Like

It’s very hard.
It’s almost work. I don’t know if the average is right, maybe it’s working just for last 2 scores.
And the average happen slow, I really don’t know when.

That’s the project: Thunkable

I don’t know whats the problem.

You may easily manually check. Using average as integer when it comes to pretty same NOTES will round the result. You get much more accurate average value if you will use 2 decimals instead.

See this:
Using LocalDB as source of DVL - Thunkable Discuss - Community
and you will see that I’m complaining about the speed when using local tables too. For Airtable, GoogleSheets is even worst when it comes to do calculations with large data…
@domhnallohanlon promised me that will provide an answer so I’m confident :blush:

Maybe in your case you can implement some trick that @muneer taught us, about making another filtered sheets from original sheets in Airtable, then use that for DVL. This way you can sum directly an entire column from filtered and speed up the app.

2 Likes

Do you think is possible do the average if I send the data to Firebase?

1 Like

Can you share your Airtable table so I can build the blocks required for the average and test it with the data?

1 Like

Example of average formula in thunkable: image
Variable added_number has the total of all the values you want to get the average of. Variable numofquest’s value is the number of numbers you previously added.

Another example:
2 + 8 = 10
10 / 2 = 5
In this example, 2 and 8 are the two values I want to get the average of, and 5 is the average number.

Hope this helps!

1 Like

Thank you but the issue is about grouping not just averaging