# 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.

---
Kind regards, HansV
https://www.eileenslounge.com

Sorry this didn't help.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

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)))

IlirU

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

Sorry this didn't help.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

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

Sorry this didn't help.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

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

Sorry this didn't help.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

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)

---
Kind regards, HansV
https://www.eileenslounge.com

·

Sorry this didn't help.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

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

Sorry this didn't help.

How satisfied are you with this reply?

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

How satisfied are you with this reply?