How to calculate average of first 12 entries in a Google Sheets column?

Other Questions Google Sheets
1

Hey everyone! I’m trying to figure out a way to get the average of the first 12 values in a column for each of my clients. The tricky part is that they all started at different times. Some have been with us for just over a year, others for several years.

I need a formula or script that can:

  1. Find the last non-empty cell in a column (which shows when the client started)
  2. Count up 12 cells from there
  3. Calculate the average of those 12 cells

Is this doable in Google Sheets? Maybe with a custom function? I know a bit of JavaScript, so I could probably follow along if that’s the way to go. Any help would be awesome! Thanks in advance!

2

I’ve actually faced this exact issue in my work with client analytics. Here’s a solution that’s worked well for me:

=ARRAYFORMULA(AVERAGE(INDIRECT(ADDRESS(MATCH(““, A:A, 0), COLUMN(A:A))&”:“&ADDRESS(MATCH(””, A:A, 0)+11, COLUMN(A:A)))))

This formula does a few things:

  1. Finds the last non-empty cell using MATCH
  2. Creates a range from that cell to 11 cells after it using INDIRECT and ADDRESS
  3. Calculates the average of that range

It’s a bit complex, but it’s reliable and doesn’t require any custom scripts. You can just copy-paste it into your sheet, changing A:A to whatever column you’re working with.

One tip: If you’re dealing with a lot of clients, you might want to put this in a helper column and then reference it in your main calculations. It’ll make your sheet run faster.

3

I’ve encountered a similar challenge in my work with client data. One effective solution is to utilize the QUERY function in combination with COUNTA. Here’s a formula that should work:

=AVERAGE(QUERY(A:A, "SELECT A WHERE A IS NOT NULL LIMIT 12 OFFSET " & (COUNTA(A:A) - 12)))

This approach first counts non-empty cells, then selects the last 12 entries, and finally calculates their average. It’s quite versatile and should adapt well to varying client histories. Remember to adjust the column reference as needed for your specific sheet layout.

If you prefer a script-based solution for more complex operations, Google Apps Script offers powerful options. Let me know if you’d like to explore that route further.

4

hey swiftcoder42! you can use average function with index and match. something like:

=average(index(a:a, match(““, a:a, 0)):index(a:a, match(””, a:a, 0)+11))

replace a:a with your column. this should do the trick! lmk if u need more help