ROLLING 30 DAY AVERAGE SHEET SUMMARY

Hi @Steve Erdahl

Try this:

=AVG(COLLECT(Prod:Prod, Date:Date, >=TODAY(-30), Date:Date, <=TODAY()))

This will take the average of the first range (Prod:Prod), but only if the other column meets the criteria specified, which is why I used the COLLECT function.

The other criteria is that the Date is either greater-than-or-equal-to (>=) 30 days ago (Today(-30)), OR that the Date is equal to today, or less (Today()).

Let me know if it works for you!

Cheers,

Genevieve

Hi @Steve Erdahl

I just realized you have commas in with the data you would like to AVG. This indicates that the numbers are actually listed as text, not as numerical data that is able to be averaged. How is that information being input? Is this from a formula or a manual entry?

If it's a formula, you could build in a VALUE function to ensure it's outputting a number instead of text... or what might be easiest is creating a helper column that simply has the formula:

=VALUE(PROD@row)

This will then create numbers and you can use this helper column as the first range in your AVG formula.

Cheers,

Genevieve

Hi @Jermain Johnson

You would want to use AVG(COLLECT instead of AVGIF since AVGIF can only identify one criteria, but in AVG(COLLECT you can specify a range, or two criteria.

Yes, you can use =TODAY(-90) for a rolling quarter!

Did you manage to get this to work?

Hi @Matt N

Yes! In the COLLECT function you can reference a range and then identify the criteria as being "greater than 0". For example:

=AVG(COLLECT({Range to Average}, {Range to Average}, > 0))

Cheers,

Genevieve

Hi @Matt N

The location of the {range} and "criteria" pair can be anywhere in the COLLECT function after the first range has been listed:

=AVG(COLLECT(range to avg, range1, criteria1, range2, criteria2))

In your test, it looks like you may have listed the [range] twice (before and after the criteria) instead of just once (before the criteria).

It happens to be the same column you want to AVG, so we'll keep the listing there at the front as the first one. Then you can go ahead to list your criteria pairs, does that make sense?

=AVG(COLLECT(Buffer Column, Buffer Column, greater than 0, Date, date criteria, Date, date criteria))

The bolded sections can be rearranged, as long as the pairs are together. So this still works:

=AVG(COLLECT(Buffer Column, Date, date criteria, Date, date criteria, Buffer Column, greater than 0))

Try:

=AVG(COLLECT([BUFFER OWREN VERONAL *17894 Usage (w)]:[BUFFER OWREN VERONAL *17894 Usage (w)], [BUFFER OWREN VERONAL *17894 Usage (w)]:[BUFFER OWREN VERONAL *17894 Usage (w)], > 0, DATE:DATE, >=TODAY(-90), DATE:DATE, <=TODAY()))

Cheers,

Genevieve