# Avrage last 6 months

Options

Hi

I'm trying to get a rolling average but cant seam to get anything to work.

my data columns are Date, Total In, Av Last 6m

Id like the Av Last 6m to average the last 6months of entries in the total in according to the date column

Thanks

• ✭✭✭✭✭✭
Options

Try this

=AVG(COLLECT([Total In]:[Total In], [Date]:[Date], TODAY(-180)))

This says collect the values from Total In where the Date is greater than today minus 180 days, and then give me the average of that collection.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• Options

hi

it returns a #divided by zero

is there a way to set it to get the date from the date column so each row will show the past 6m from that date?

thanks

• Options

so i worked out i needed to add a grater than to the formula =AVG(COLLECT([Total IN]:[Total IN], Date:Date, >(Date1 - 180))) and this gave me the avrage for dates grater than the calculated date -180 days

i also need to add a condition for less than the date in the date colum so it will only avage the totals between the entered date and 180 days before.

any ideas?

thanks

• Options
• ✭✭✭✭✭✭
Options

Try something like this...

=AVG(COLLECT([Total IN]:[Total IN], Date:Date, AND(@cell > Date1 - 180, @cell <= Date1)))

• ✭✭✭✭✭✭
Options

Sorry I missed that greater than symbol - this board does a weird thing where, if I hit the space bar after a greater than symbol, it erases the symbol!

Glad I point you in the right direction, and thanks to Paul for finishing it off. Hope it works for you.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• Options

thanks so much for the help.

my last one i cant solve is

=AVG(COLLECT([Total IN]:[Total IN], Date:Date, DATE(MONTH(7))))

im trying to get my next column to average the total in for any entry that is in that month (past years included)

any ideas

thanks

• ✭✭✭✭✭✭
Options

=AVG(COLLECT([Total IN]:[Total IN], Date:Date, MONTH(@cell) = 7))

You want to pull the MONTH on a cell by cell basis and determine if that equals 7 would be the logic behind the above.

• Options

thanks for the help i worked out if i extracted the month into a column i could reference it so when a new row is added all works by its self

=AVG(COLLECT([Total IN]:[Total IN], Date:Date, IFERROR(MONTH(@cell), 0) = Month1))

is what i ended up with.

thanks

• ✭✭✭✭✭✭
Options

@jeremy Faulkner Happy to help. Glad you were able to find a working solution. The formula I provided SHOULD have done the same thing but skipping the step of extracting the month number into a separate column.

• Options

Hey guys,

Can somebody knows how to let this formula work "=AVG(COLLECT(price2:price18; date2:date18; MONTH(@cell) = 2))",

when one of the many cells are empty? Since I want to count only filled cells.

• ✭✭✭✭✭✭
Options

When cells within which range are empty? Price or date?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!