Weekly Avg

Melanie Bryant

Hello

I have a sheet that calculates days opened for an item. I want to calculate the avg days open each Monday based on open items. Then build a graph to display that info. I'm stumped on how to best do this? Each time I create a formula I realize I'm not getting the value I set out to achieve.

This is my formula to pull the avg, I just can't figure out what to add take that snap shot each Monday.

=AVG(COLLECT({Days Open}, {Disp Action Comp}, 0))

I could have used Avgif as well, but when I started I had more than one criteria and I just left it as is.

Thanks

Melanie

Jeff Reisman

@Melanie Bryant

First, create a "Current Date" date column, and create an automation to record the current date at 1am everyday in that field.

Let's use an IF to check if the date has reached the Monday date yet, and if it has, find the current average.

=IF([Monday Date]@row <= [Current Date]@row, AVG({Days Open}))

But wait! Even though each Monday the latest row gets updated, won't this mean that every past row has the current average days open? Yes, you're right! So how do we get around this?

What we're going to do is set up an automation that, each Monday, copies that week's row to a metrics sheet. When you copy rows, only the data result gets copied, not the formula!

Automation Trigger: When a Date is reached, indicate the Date field (Monday Date), and then a time of day to do the copy (make sure that it's after the Current Date automation you created above!)

Condition: Where Monday Date is today

Action: Copy row to metrics sheet.

Now for your graph:

Build a report based on your metrics sheet. It will pull all the Monday Date values and the Average Days open for each. Use that report as the basis for your graph.