Calculate average percentage across dates (Solved)

New Member
Hi,

In my data set that contains percentages, I need to calculate their average on a day by day basis. With each passing day, the number of days needed to calculate the average will reduce. I need a solution where on dragging the formula rightwards, the number of days considered to calculate the average will reduce one by one automatically.

For example, the day 1 average will comprise of data from dates 1/1/ 2006 to 11/1/2006. 12/1/2006 will not be considered as data for that day is yet to be computed.
Similarly, the day 2 average will comprise of data from dates 1/1/ 2006 to 10/1/2006. 11/1/2006 and 12/1/2006 will not be considered as data for those days is yet to be computed.

Attaching the file for your perusal.

Attachments

• Chandoo Help March 13.xlsx
10.7 KB · Views: 4
Last edited by a moderator:
Depending on your XL version perhaps (pulled right)
=AVERAGEIF(B\$2:B\$13,"<>0",B\$2:B\$13)
or
=AVERAGE(B\$2:INDEX(B\$2:B\$13,MATCH(0,B\$2:B\$13,0)-1,1))
and many others

Last edited:
Hey pecoflyer,

both the methods work well.

Thank you so much!!

You're welcome

hi,
Thank you, Pecoflyer for the solution.