ROLLING 30 DAY AVERAGE SHEET SUMMARY

Steve Erdahl

Hello,

I'm looking to build a formula in the Sheet Summary that will provide a rolling 30 day average from the PROD values. (attached is a data subset) Feels like this should be fairly basic, but am struggling to find the solution. Anyone's help would be appreciated.

Genevieve P.

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

Steve Erdahl

HI Genevieve,

Thank you so much for helping. Your assessment is exactly what I'm looking to do.

When I input your equation, i get a "#DIVIDE BY ZERO" result. Any thoughts as to why?

Appreciate the support!

Genevieve P.

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

Steve Erdahl

I have the "Thousands format" turned on to this column which adds the comma - visual preference. I tried the calc without the commas, but still get the #DIVIDE BY ZERO result.

L_123

=averageif(date:date,>=today(-30),prod:prod) is a simpler way to do this.

Check the dates. Depending on how you input them it might be 1920 instead of 2020 for the year.

Steve Erdahl

Is it an issue that I have each date represented twice, once for a day shift and another for a night shift?

Alexander Ford

@Steve Erdahl my best guess on this is that your PROD column has text values and not numerical values, going back to what @Genevieve P mentioned.

The reason this is likely, is that using the AVG function against text values will always return a #DIVIDE BY ZERO result. Creating a helper column and using =VALUE(PROD@row), then calculating using that helper column's data instead of your PROD column should do the trick!

Thanks,

Alex

Jermain Johnson

https://community.smartsheet.com/discussion/comment/263267#Comment_263267

Can this solution be used with avgif formula and rolling by quarter? Or would I just do =today (-90)?

Genevieve P.

https://community.smartsheet.com/discussion/comment/298483#Comment_298483

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?

Matt N

Hi @Genevieve P.

Is there a way to only pull positive numbers from the data set for the average?

thanks

Genevieve P.

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

Matt N

@Genevieve P.

I have tried to place both the greater than zero AND rolling 90-day conditions in the formula and I get the #INVALID OPERATON result. Is there an order it has to come in? For example, do I need to set the greater than 0 condition first then the date condition or vice-versa or does it not matter?

Genevieve P.

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

Matt N

@Genevieve P.

That Worked! You're the best! Thanks!