Calculate average of last few cells dynamically in spreadsheet

Other Questions Google Sheets
1

I’m working on a spreadsheet and need some help with a formula. Right now I’ve got about 20 rows of data in column D. I want to find the average of the last 7 values, but I don’t want to type out the cell range manually. Is there a way to make this work automatically even when I add more rows later?

Here’s what I’m using now, but it’s not flexible:

=AVERAGE(D14:D20)

I’m hoping there’s a smarter way to do this so I don’t have to keep updating the formula every time my data grows. Any ideas on how to make it adjust on its own? Thanks for any tips!

2

I’ve faced a similar challenge in my work, and I found a solution that might help you out. Try this formula:

=AVERAGE(INDEX(D:D, COUNTA(D:D)-6):INDEX(D:D, COUNTA(D:D)))

This approach uses the INDEX function to create a dynamic range for the last 7 cells with data in column D. It’s quite flexible and will automatically adjust as you add more rows.

The formula works by finding the last cell with data using COUNTA, then creating a range from 6 cells before that to the last cell. This way, you’re always averaging the last 7 values, regardless of how many rows you have.

I’ve been using this method for a while now, and it’s saved me a lot of time and hassle. Give it a try and see if it meets your needs.

3

hey there! heres a neat trick i use:

=AVERAGE(D:D) - AVERAGE(D1:INDEX(D:D,COUNTA(D:D)-7))

it always grabs the last 7 values, no matter how many rows u add. Pretty cool right? lemme know if u need any help with it!

4

You can use a combination of OFFSET and COUNTA functions to create a dynamic range for your average calculation. Here’s a formula that should do the trick:

=AVERAGE(OFFSET(D:D,COUNTA(D:D)-7,0,7,1))

This formula will always average the last 7 non-empty cells in column D, regardless of how many rows you add. It works by counting the number of non-empty cells in column D (COUNTA), then subtracting 7 to get the starting point for the last 7 cells. The OFFSET function then creates a range from that point, spanning 7 rows and 1 column. This range is then fed into the AVERAGE function.

Hope this helps solve your problem efficiently!