Hi,
I have the following list of values that differ between 45 and 55.
I would like to add another column containing the moving average of the values.
But I am somehow lost in implementation. Do you have any idea of how to implement this?
If a moving average is not possible, calculating the average would be nice.
Hi There!
I created an example with a portion of the data from your screenshot - take a look here:
https://public.getgrist.com/4pHj4AuDtDuw/Community-742-Calculating-Moving-Average/p/m/fork
For the rolling average, the formula currently takes the average from the last 7 calendar days. You can adjust that as you’d like.
There’s also an overall average in the table on the left. This is a summary table where I added the average. To add a summary table, click the green ‘Add New’ button > Select Widget = Table > Select Data = Table where your data is > click green summation button > Add to Page. This will add the Count and Values columns you see on mine. The value in the Values column is a sum of all of the values in the data table. To get the average, divide this number by the count. It will update as you add more values to your data table.
Please note that beyond 100 rows it will get increasingly slower. One of our developers tested this and had to wait ~10 seconds when adding a new date past 500 rows.
Hope this helps!
Tried that formula adapted to a table with 5000 rows. It’s taking ages for the calculation of all rows.
Also, I need to calculate the moving average of only the rows with the same reference. Let’s say I have a table of products and a table of product prices at different days.
The product prices table has a reference to specific products… then the price and a date.
example
bread - 5 - 01/02/24
bread - 5,2 - 01/03/24
bread - 5,25 - 01/04/24
bread - 5,3 - 01/05/24
bread - 5,29 - 01/06/24
oranges - 4 - 01/02/24
oranges - 4,1 - 01/03/24
oranges - 4,2 - 01/04/24
so how would I get the moving average of the last 4 periods of orange (it only has 3 btw) without calculating bread too?
And this topic is two years old. Maybe there is a new faster way to calculate it?