2

I have a spreadsheet that looks like this

enter image description here

I want to average only the previous 5 cells that have data in them. How can I do this?

As the list gets more data, like this:

enter image description here

I want to be able to target a cell (like A10) and go 5 back from that. Again, only if it has data in it.

So the formula for the image should be like this: (23 + 60 + 53 + 42 + 40)/5 = 43.6

It's going backward and skipping the black space, yet still getting the 5 previous.

player0
  • 124,011
  • 12
  • 67
  • 124
Cam
  • 988
  • 1
  • 12
  • 25

1 Answers1

2

The following formula assumes your values are on column A (adjust all A:A references if they're not):

=Average(Filter(Sort(A:A;If(A:A="";0;Row(A:A));false);Row(A:A)<6))

To understand the formula above, paste its parts separately (when you get a part without the Filter formula, you'll need to wrap it on an ArrayFormula. e.g.

=Filter(Sort(A:A;If(A:A="";0;Row(A:A));false);Row(A:A)<6)
=ArrayFormula( Sort(A:A; If(A:A="";0;Row(A:A)); false) )
=ArrayFormula( Row(A:A) )

Place these "auxiliary" formulas on empty columns you might have on the sheet (in the first cells, e.g. D1, T1).

For non-entire columns, like you suggested, you may also need to wrap the whole formula in an array formula, e.g.

=ArrayFormula( Average(Filter(Sort(K1:K10;If(K1:K10="";0;Row(K1:K10));false);Row(K1:K10)<6)) )

I initially thought that Filter formula would behave like the ArrayFormula, but it seems it does not "recursion" down to the inners of its parameters. Hance, the ArrayFormula is required for the IF to work properly. I do not know why it did work for A:A though.

Henrique G. Abreu
  • 17,406
  • 3
  • 56
  • 65
  • Yeah, but I would like the 5 previous with values. So, say there were more values going down, I would only want to go 5 back from it, unless there was nothing in a cell, then it would need to go back 6. Does that make sense? So the 5 previous values, not necessarily the 5 previous cells. – Cam Apr 27 '12 at 19:28
  • Sorry, I understood your question totally wrong. Hope this is the correct/expected answer now. – Henrique G. Abreu Apr 28 '12 at 22:26
  • there's an "extra" parenthesis on the formula. I fixed it. Please try – Henrique G. Abreu May 02 '12 at 00:01
  • Take a look at my revised question. I don't understand how I can target a cell and go "back" or "up" from there. – Cam May 03 '12 at 14:03
  • It's sorting the entire row. I only want the previous 5 from the target cell. – Cam May 03 '12 at 18:22
  • I mean, it looks to me like I can't target a cell using the formula you are providing. I should be able to call K10 and have it automatically find the 5 previous cells going up that have values. Then average those. – Cam May 03 '12 at 18:34
  • Oh! That's the problem? I though you'd always want the real last 5. Anyway, just limit the range e.g. for `K10` change all `A:A` ranges to `K1:K10` – Henrique G. Abreu May 03 '12 at 18:43
  • I don't want the sort, but I do want to filter out the ones that have no value. and I only want the previous 5 that have a value. – Cam May 03 '12 at 18:44
  • The `Sort` is just a technique to get the previous 5. It doesn't matter for the `Average`. I don't see a problem with it. What's the issue? – Henrique G. Abreu May 03 '12 at 19:04
  • =Filter(Sort(K1:K10;If(K1:K10="";0;Row(K1:K10));false);Row(K1:K10)<6) I get a "Range has no entry corresponding to this cell" error. – Cam May 03 '12 at 19:19
  • I have edited my answer, hopefully providing the correct answer. Have you seen it? – Henrique G. Abreu May 05 '12 at 14:31