# Average Data Between Certain Months

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• Hi there,

I have one year of Dates between B2:OF2

I have one year of Data between B6:OF6

In one cell I would like to take the average of data from months between November 1 - March 31.

In another cell I would like to take the average of data from months between April 1 - Oct 31.

The spreadsheet will continuously have one year of data so right now the spreadsheet has April 1, 2020 to April 30, 2021. Next month I'll drop April 2020 and add May 2021. So I'm hoping to do this without having to adjust the formula going forward.

Thanks for your help.

• Sorry about that.....example workbook as requested.

## Files

• Thanks...I can make that work.

Is there also way of doing it to search for month/day and not the year. So when I add more data I don't need to update the from and to dates?

• Ok, that ok.

Here is another scenario that I was looking to do that involves many numerical locations that I was hoping you could help me with. I've added a few lines as an example of what I am looking at.

Thanks,

## Files

• Change your D16 formula >>

From this :

=ROUND(AVERAGEIFS(\$C\$2:\$OG\$2,\$C\$1:\$OG\$1,">="&A16,\$C\$1:\$OG\$1,"<="&B16),1)

Into this :

=IFERROR(1/(1/ROUND(AVERAGEIFS(OFFSET(\$C\$1,MATCH(\$C16,\$B\$2:\$B\$7,0),,,395),\$C\$1:\$OG\$1,">="&A\$16,\$C\$1:\$OG\$1,"<="&B\$16),1)),"")

Then,

copied to I16, and all copied down

Regards

• Thank you so much...formulas work beautifully.

Palchy

• One last question.....if I have only monthly totals instead of daily how would my formula change. I used the existing one and it returned nothing.

Attached file for example.....

Palchy

## Files

• One last question.....if I have only monthly totals instead of daily how would my formula change. I used the existing one and it returned nothing.

Attached file for example.....

Palchy

Since your header range is changed to B1:N1, the Offset 5th argument must changed to 13 in match with the size of range,

So,

The D6 formula changed to >>

=IFERROR(1/(1/ROUND(AVERAGEIFS(OFFSET(\$B\$1,MATCH(\$C6,\$A\$2:\$A\$3,0),,,13),\$B\$1:\$N\$1,">="&A\$6,\$B\$1:\$N\$1,"<="&B\$6),1)),"")

• Yes, that works. I was wondering what the ,,,395 meant in the first formula, now I know.

Thanks,

Palchy

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!