2

I am trying to find the average of the seven most recent entries in a row, as seen in this spreadsheet.

I found a few questions similar to mine, but I am still pretty confused on how the answers work. The questions similar to mine can be found on the left side of my spreadsheet.

I think that the formulas would work for me with a few simple adjustments of which values to use, but I can't seem to figure it out. I would really appreciate if someone could explain one of the existing answers or come up with another one that works.

The spreadsheet is updated daily, so I need something that would continue to work as more and more data is added to the column.

Community
  • 1
  • 1
beninato
  • 420
  • 5
  • 15

2 Answers2

3

Try:

=round(AVERAGE(OFFSET(H1,MAX(ARRAYFORMULA(ROW(H:H)*--(H:H<>"")))-7,,7)))

here's working sample

Explanation

We are getting the last non empty row: MAX(ARRAYFORMULA(ROW(H:H)*--(H:H<>"")))

Then with offset formula we are getting the range of last 7 cells in a column.

And then just use AVERAGE.

More info

You may find more info about finding the last non empty row here: Selecting the last value of a column

Community
  • 1
  • 1
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
2

Another way is to use INDEX and MATCH. The first match finds the position of the last number in the range and takes 6 away from it: the second match finds the position of the last number in the range. Passing it through the INDEX function gives a reference that you can use to give a range of 7 cells for AVERAGE to work on.

=average(index(H:H,match(999,H:H)-6):index(H:H,match(999,H:H)))

So my answer is like your Link2

The big snag here is if you have a text cell in the range (like "Nothing") it is much more difficult to work out which cell to start from to get an average of 7 cells. I think I know how to do it in Excel using OFFSET but offset doesn't seem to work in the same way in Google Sheets.

However I can see there is a solution to this in your Link3 which should work for you if you change A:A to H:H and SUM to AVERAGE. I have tested it on the average of the last ten cells which includes a "Nothing" cell:

=ArrayFormula(AVERAGE(QUERY(SORT(H:H,ROW(H:H)*ISNUMBER(H:H),0),"select * limit 10")))

and it gives the correct answer 61.8.

The way array formulas work in general is that instead of passing a single value to a function you pass a whole range or array (a list of values) and the function processes them one by one. The above formula takes the whole column H:H and sorts it on the row numbers in descending order but those cells which don't contain a number give zero in the multiplication and are sorted to the bottom. Then the query takes the top (in my case) 10 cells and passes them to AVERAGE.

BTW this doesn't have to be declared as an array formula: this also works

=AVERAGE(QUERY(SORT(H:H,ROW(H:H)*ISNUMBER(H:H),0),"select * limit 10"))
Community
  • 1
  • 1
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Thanks for the response; however, I will probably use Max's answer. I see that you also use the array formula function, could you explain what it does? – beninato Nov 24 '16 at 15:40
  • A will add an explanation of the second formula to my answer (the first one isn't an array formula) – Tom Sharpe Nov 24 '16 at 15:42