Ask a new question

excel formula for calculating a running average as new data is entered

I've tried multiple formulas but not working. I have a set of data that I enter per month. I have a table that calculates the average for each month per person. However I need a formula that will automatically calculate the running average as new data is entered month to month. 

So in the last row I want to have a formula that will calculate the running average as data is entered every month. For example, as of today the average should be from Jan to Aug. When Oct hits, then data will be entered for Sept; then I need the Average to include Sept automatically and so forth.

Note: there is existing formula in the table under each person per month (=IF(B13=0,0,M13/B13) to account for zeros. This pulls from other tables that I have on the same sheet. 

Table is as follows:

  MARY JOEL JACK TERRI PAUL TIM PAM BRANDI TOM
JANUARY 0.00% 0.00% 15.38% 0.00% 2.94% 0.73% 12.32% 0.00% 0.00%
FEBRUARY 0.00% 16.67% 16.67% 0.00% 6.33% 1.36% 14.29% 0.00% 0.00%
MARCH 8.00% 0.00% 0.00% 0.00% 2.61% 1.22% 12.02% 0.00% 0.00%
APRIL 10.34% 3.93% 10.53% 10.85% 0.00% 16.67% 33.33% 0.00% 0.00%
MAY 0.00% 4.24% 8.54% 15.04% 0.00% 0.00% 0.00% 0.00% 0.00%
JUNE 12.00% 2.80% 9.88% 7.97% 0.00% 7.14% 0.00% 0.00% 0.00%
JULY 0.00% 0.00% 0.00% 0.00% 11.97% 4.51% 11.36% 0.00% 0.00%
AUGUST 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00%
SEPT 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
OCT 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
NOV 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
DEC 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
AVERAGE 16.29% 15.96% 20.13% 16.73% 15.48% 16.46% 22.91% 12.50% 12.50%

Let's say the month names are in A2:A13 and the person names in B1, C1, etc.

The running average for column B would be

=IF(MONTH(TODAY())=1,0,AVERAGE(OFFSET(B$1,1,0,MONTH(TODAY())-1,1))

This formula can be filled or copied to the right to column C, D, etc.

---
Best wishes, HansV
https://www.eileenslounge.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Darrell A,

 

Try below formula in cell B14 and copy it right till J14.

 

=AVERAGE(INDEX(B2:B13,1):INDEX(B2:B13,MATCH(LOOKUP(9.99E+307,1/B2:B13,B$2:B$13),B2:B13,0)))

 

Hope this helps. If yes, please mark my reply as Answer.

 

IlirU

---------------
Regards, IlirU

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

as I add data to test formula it stops at August. It doesn't include the subsequent months (sept, oct, etc...) of new data. 

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

didn't work when I added data for sept, oct, etc...

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I thought you wanted to calculate the average for January-August while it's September (regardless of whether data for September and subsequent months have been entered or not). Apparently I was mistaken.

Perhaps you want

=IFERROR(AVERAGE(OFFSET(B$1,1,0,COUNTA(B$2:B$13),1)),0)

---
Best wishes, HansV
https://www.eileenslounge.com

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi,

In cell B14, enter this formula and copy down

=AVERAGE(B2:INDEX(B2:B14,MATCH(TEXT(EOMONTH(TODAY(),-1),"mmmm"),$A$2:$A$14,0),1))

Hope this helps.

Regards,

Ashish Mathur
www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated October 5, 2021 Views 608 Applies to: