Average formula

sahilhq

Hi,

Can you suggest me with a clever way of calculating average for a group of cell references? Please see attached screenshot of my sheet. Anytime the data for a new month is entered into my sheet, I need to manipuate the average 6 months formula so that calculates the average for the latest 6 monthly only. Is there any way I can have a formula that will be able to make these changes for me without having to manipulate it each time?

avg snapshot.PNG

Paul Newcome

You could insert a date type column that houses the first of each month on each row.

Then insert a checkbox column with a formula such as below to check the boxes for the rows containing the previous six months:

=IF(AND([Date Column]@row >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1)), [Date Column]@row <= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), 1)

The you can use an AVERAGEIF statement to average all of the numbers in the rows where the boxes are checked.

=AVERAGEIF([Checkbox Column]:[Checkbox Column], 1, [No. of Visits]:[No. of Visits])

Paul Newcome

Because we are basing it off of month and year, it will check the box on the first of the first month and remain checked through the last day of the last month.

To adjust for different durations, you just need to remember that there are 12 months in a year. The first MONTH function will subtract however many months you wan to go back, and the second month function would be adding months using the number that you would add to the first to equal twelve.

So seven months would be -7 and +5. Eight months would be -8 and +4. Five months would be -5 and +7, etc etc....

=IF(AND([Date Column]@row >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 7, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 5, 1)), [Date Column]@row <= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), 1)

Paul Newcome

You could insert a date type column that houses the first of each month on each row.

Then insert a checkbox column with a formula such as below to check the boxes for the rows containing the previous six months:

=IF(AND([Date Column]@row >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1)), [Date Column]@row <= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), 1)

The you can use an AVERAGEIF statement to average all of the numbers in the rows where the boxes are checked.

=AVERAGEIF([Checkbox Column]:[Checkbox Column], 1, [No. of Visits]:[No. of Visits])

sahilhq

That's an absolute masterpiece of a formula and solution. Thanks Paul!

Paul Newcome

Happy to help! 👍️

sahilhq

Hi @Paul Newcome, Just a quick follow up question on the formula. Can you please tell me how the formula would look like if it was calculating the previous 7 months instead of 6? I guess it would only be minor changes? Also, on which date of the month does the box get checked? I guess it will on the first of every month? Thanks once again!

Paul Newcome

Because we are basing it off of month and year, it will check the box on the first of the first month and remain checked through the last day of the last month.

To adjust for different durations, you just need to remember that there are 12 months in a year. The first MONTH function will subtract however many months you wan to go back, and the second month function would be adding months using the number that you would add to the first to equal twelve.

So seven months would be -7 and +5. Eight months would be -8 and +4. Five months would be -5 and +7, etc etc....

=IF(AND([Date Column]@row >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 7, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 5, 1)), [Date Column]@row <= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), 1)

sahilhq

Understood. Thank you :)

Paul Newcome

Happy to help. 👍️