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, 2022 in Others by Kithuzzz
• 38,010 points
475 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, 2022 by narikkadan
• 63,160 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 Nov 26, 2022 in Others by narikkadan
• 63,160 points
296 views
0 votes
1 answer

Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

answered Jan 9 in Others by narikkadan
• 63,160 points
164 views
0 votes
1 answer

Excel formula to calculate MIN in table filtered

Try this: =SUBTOTAL(105;B2:B7) READ MORE

answered Jan 30 in Others by narikkadan
• 63,160 points
188 views
0 votes
1 answer
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, 2022 in Others by Edureka
• 13,670 points
567 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, 2022 in Others by Edureka
• 13,670 points
1,008 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, 2022 in Others by Edureka
• 13,670 points
474 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,220 points
1,420 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, 2022 in Others by narikkadan
• 63,160 points
1,084 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 Nov 24, 2022 in Others by narikkadan
• 63,160 points
569 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