Avrage last 6 months

jeremy Faulkner

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

Jeff Reisman

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.

jeremy Faulkner

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

jeremy Faulkner

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

jeremy Faulkner

Paul Newcome

Try something like this...

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

Jeff Reisman

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.

jeremy Faulkner

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

Paul Newcome

=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.

jeremy Faulkner

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

Paul Newcome

@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.

Nerijus Jurgutis

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.

Paul Newcome

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