Need to find the average weight for a particular month over many years

**Miller217** · 09-30-2014, 01:39 PM

I need to figure out what formula to use to find the average weight for an animal during a particular month spanning several years.

Column A1 is labeled "Date" and the date is formatted as MM-DD-YYYY it has dates from 2008 - 2014 and is a continually updated spreadsheet.
Column B1 has the corresponding weight for that particular day, also frequently updated.

What I would like to do is start another tab that continually updates what the average weights are for a given month. Say, the average weight for the month of September, since this animal's weight is based seasonally. Is there a way to do this that would update continually as I enter in new dates and weights?

I think I would be using an AVERAGEIF formula, but I really have no idea.

**BadlySpelledBuoy** · 09-30-2014, 01:43 PM

Could you post a small sample of your data in a workbook?

**Miller217** · 09-30-2014, 02:03 PM

Hope I did this correctly! KWeight.xlsx

**BadlySpelledBuoy** · 10-01-2014, 12:46 PM

Apologies for the delay. Had a few IT issues!

Anyway, see attached for two approaches to this. One using SUMPRODUCT and one using AVERAGEIFS and a helper column.

I'm sure there are many other ways, but these will get you going.

As for having it update automatically as you add more data, you can use dynamic named ranges to accomplish this but the "helper column" approach would involve copying the formula in the helper column down. Again there are several ways of doing this.

BSB.