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,020 points
1,191 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,720 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,720 points
1,350 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, 2023 in Others by narikkadan
• 63,720 points
622 views
0 votes
1 answer

Excel formula to calculate MIN in table filtered

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

answered Jan 30, 2023 in Others by narikkadan
• 63,720 points
473 views
0 votes
1 answer

Excel AVERAGEIF function in combination with MOD to average over every nth row not behaving as expected

Average Every Nth Row All three finish in ...READ MORE

answered Mar 27, 2023 in Others by narikkadan
• 63,720 points
356 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, 2022 in Others by Edureka
• 13,690 points
3,610 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,690 points
1,890 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,690 points
935 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,260 points
3,479 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,720 points
2,361 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,720 points
1,384 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