Moving Average formula in Excel not autofilling in table

0 votes

I recently made an excel table to keep track of my weight, and I wanted to add a column for the 7-day moving average.

The issue I'm now having is that when I create a new item, the Moving Average column doesn't automatically fill in. I used this reference to develop the Moving Average formula: Data analysis was available at https://www.deskbright.com/excel/rolling-average-in-excel/.

Here is the moving average formula:

Here is the moving average formula

The problem is that when I create a new entry, like this

image

The Average Weight formula autocompletes, whereas the Moving Average formula does not. The 7-Day Average column's entries for 6/28 and 6/29 in the image are blank. The formula displayed is for the average weight and uses a standard 7-day workweek rather than rolling.

I know it's very easy to fill it out by dragging the formula down, but is there a way to have it autocomplete?

Oct 1 in Others by Kithuzzz
• 20,660 points
54 views

1 answer to this question.

0 votes

You could use AVERAGEIFS to make the formula auto-fill and COUNTIFS to guarantee that you only return a number if the table contains the correct date range for a 7-day moving average:

=IF(COUNTIFS(A:A,">="&A2-6,A:A,"<="&A2)<>7,"",AVERAGEIFS(B:B,A:A,">="&A2-6,A:A,"<="&A2))
image
answered Oct 2 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

What does the " @ " symbol mean in Excel formula (outside a table)

Excel has recently introduced a huge feature ...READ MORE

answered 2 days ago in Others by narikkadan
• 37,660 points
6 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
201 views
0 votes
1 answer

Subtotal in result row returns 0 in excel table

Syntax SUBTOTAL(function_num,ref1,[ref2],...) The SUBTOTAL function syntax has the following ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
53 views
0 votes
1 answer

Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

answered Sep 30 in Others by narikkadan
• 37,660 points
53 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
139 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
234 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
102 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18 in Others by gaurav
• 22,040 points
69 views
0 votes
1 answer

Text with a dash in the cell but not in the formula bar (Excel)

What you have got here is called ...READ MORE

answered Nov 13 in Others by narikkadan
• 37,660 points
24 views
0 votes
1 answer

How to use an increment an average formula by more than one row in excel?

I believe OFFSET makes it simpler, for ...READ MORE

answered 4 days ago in Others by narikkadan
• 37,660 points
15 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP