Calculating daily and last 30 days averages based on form submissions

Kylie Jones

Hello,

I am collecting daily form submissions and want to have a dashboard to report on key metrics from these submissions. See screenshot.

Here's what I want to calculate:

• Daily average for columns Role, Identity, Technique, Quality of Day, and My Score Today. At the end of the day I need to see what TODAY's average number is for each of those columns, using only data collected on today's date.
• 30-day rolling average for each of the above mentioned columns.

I'm guessing I need to calculate this in the cells above, to be able to put onto a dashboard with a barometer type widget.

Can anyone help with the correct formula to display these numbers please? Can I then push these numbers through to a Smartsheet dashboard?

@Paul Newcome

Paul Newcome

I would suggest a separate metrics sheet. One column would be dates where you have a rolling 30 day period. My suggestion for this would be a text/number column (called "number") with the numbers 30-0 going down.

Then in the date column:

=TODAY() - Number@row

What should happen now is you have today's date on the bottom row and then decreasing as you go up.

Then you would run your calculations for your averages based on these dates. Something like...

=AVG(COLLECT({Form Sheet Role Column}, {Form Sheet Entry Date Column}, @cell = [Date Column]@row))