Annual Averages

[Manesh]

Dear Chandoo,

I am seeking to calculate the annual average rate. I have data for various dates in each calender year. I am seeking to write a formula to workout the annual average from the first date of the year to the last date of the year.

In other words, by formula, we need to establish which is the first date and which is the last date and then calculate the average.

The average must appear in Column C on the last date for the year. Example Cell C262 and C522.

I have used the average formula in Column D to provide an indication of the answer I am searching for. The problem with my method is that I have blank cells in between and if the dates change, then I am unlikely to obtain the correct answers.

I have attached a data sheet herewith.

Thank you.

Best regards,
Manesh

 

[Somendra Misra]

Hi Manesh,

See the file.

Regards,

 

[Nebu]

Does this serve your purpose.

 

[jeffreyweir]

I endorse Nebu's approach here: PivotTables provide a very easy-to-use and easy-to-follow method of solving questions like these.