I’m trying to add all the values in one column of a Google Sheet I’m using as my database, and then find the average of all the values, but I don’t know any way to do it. If anyone has a suggestion or solution, it would be nice! Thanks!
Do you want the easy way or the hard way?
The easy way is to create the average formula in the Google Sheet and then access that cell from Thunkable.
If you want the hard way, let me know.
Yes, Just create a average formula in the column you want and get the value from there
@1685249d1m, if you want to get the average value of a column using Thunkable blocks, you can use a Data Source block to get your column, then use List blocks to get the average. As you can see in this screenshot, there are a lot of values you can get from a list of numbers! For this block, ‘average’ = ‘mean’, ie. the sum of all of the values divided by the number of values.
Edit: you do have to cast your values as strings first. I have amended my blocks to show a simple function you can add to your app which will allow you to convert a list of numbers to be recognized as numbers in your app:
I tried your solution but for some reason when I attempted to display the variable into a label, instead it showed a string of random numbers much larger than the average. Do you have any ideas why this is so? Thanks!
The hard way would be useful as I’m having difficulties averaging the columns, because then the average is displayed in my list viewer, which isn’t something I want.
The block [List of values] assumes the column content is text this is why you cannot add them together.
You need to get [List of values] and loop through it one by one adding them to a variable and add to it +0 to force the content to contact nvert to number. Then decide the total by the number of items in the [List of values] list to get the average.
This is the hard way.
Do you know how I could loop or what blocks I should use to accomplish this? Thanks!
You can try CSV export with SQL query to get formatted data from the Google Sheets.
According to your need you want the sum of total cell values of a column. So for that you can use
For Example :
Here A in Sum(A) is the first column if my sheet
Another solution is to convert the actual values in the list to numbers then use the average function in the list block to get the same.
It produce the same result.
Ah, okay I understand now. Thanks, this helped a lot!
Ding ding ding! @muneer comes through with the hard way.
Honestly, I’d probably just create a new column in the Google sheet and store the average there. But now you have a couple options.