Square Wave Averages

Hi.

Not the most succinct, but:

=AVERAGE(INDEX(A:A,15+57*ISEVEN(ROWS($1:1))+300*(CEILING(ROWS($1:1)/2-1,1))):INDEX(A:A,67+243*ISEVEN(ROWS($1:1))+300*(CEILING(ROWS($1:1)/2-1,1))))

in your first cell of choice and then copied down will give results equivalent to, successively:

=AVERAGE(A15:A67)
=AVERAGE(A72:A310)
=AVERAGE(A315:A367)
=AVERAGE(A372:A610)
=AVERAGE(A615:A667)
=AVERAGE(A672:A910)

...etc.

Regards

So your first set is 238 points (A72:A310), then 52 points (A315:A367) then 238 (A372:A610) then 52 and so on, with 5 points between?

Originally Posted by reehilch

Thank you so much that worked when I dragged it down manually, but I have a lot of data ~200,000 points and excel timed out and crashed when I tried to repeat over the whole set. Is there a way or ratio of cells I need to drag it down to specifically?

To how many cells did you copy the formula?

Are you not able to determine this value by performing a simple calculation based on your dataset, so that you know precisely to how many rows you will need to copy this formula?

Regards

If you copy this formula down in the next column, it will give the last row in A used by the cell to the right. Then you can see when your final point, i.e. 201234 is taken into account and stop dragging. Does that help?

=67+243*ISEVEN(ROWS($1:1))+300*(CEILING(ROWS($1:1)/2-1,1))

I was thinking more along the lines of, based on the ranges you give, you will require 6 formulas for every 1,000 rows: hence (200,000/1,000)x6 = 1,200 formulas.

Regards