0

Let's say I want to do a 10 line moving average over multiple columns. To simplify, I made this visual example:
EXAMPLE

I want to enter the algo into cell C12 and fill down. It should take a 10 line average, starting with column 1. As column 1 ends, or has blanks, more of the 10 will be taken from column 2, until it is only column 2.

C12 should equate to: =(sum(A3:A11)+B12)/10

C13 should equate to: =(sum(A4:A11)+sum(B12:13))/10

C14 should equate to: =(sum(A5:A11)+sum(B12:14))/10

2 Answers 2

1

I decided to try harder to figure it out myself. The following works well:

=IF(COUNT(B$12:B12)>=10,AVERAGE(INDIRECT(ADDRESS(ROW()-9,COLUMN(B12))):B12),(SUM(A3:A$11)+SUM(B$12:B12))/10)

Thanks again.

0

Start with

=(sum(A3:A$11)+sum(B$12:B12))/10

Copy down. Screenshot uses =formulatext(G4) copied down to show formulas

enter image description here

Edit

If there is only ever one number between columns A and B, then your formula can be a lot simpler. In C12 you can use

=Sum(A3:B12)/10

Copy down.

6
  • If that resolved your issue, please mark the answer as described in the tour. If it didn't, please leave a comment, so I can follow up.
    – teylyn
    Sep 27, 2022 at 2:28
  • Thank you for your answer, but unfortunately I think this does not work. Eventually I would have way more than 10 numbers added together in "Column 2". For example Sep 27, 2022 at 4:35
  • Thank you for your answer but unfortunately this does not work. Eventually I would have way more than 10 numbers added together. For example, when we eventually have all the numbers in column 2, then dollar sign on B$12 would have to be removed to maintain 10 numbers. I don't want to always do this manually. Sep 27, 2022 at 4:37
  • I edited my answer.
    – teylyn
    Sep 27, 2022 at 23:12
  • No, sorry, this is just a simplification, so that won't work. The reality is more complicated. The other column is in another sheet and contains a lot more data, and I have to do this many times. So, it needs to start on one column and end on the other, and eventually switch, not just do both columns at once. Sep 28, 2022 at 7:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .