1

I'm trying to have a spreadsheet with a second standings chart that references a lot of different information on the sheet; I'm trying to average a range of a row of cells, but the rows that have the numbers I'm using are 5 rows apart.

=AVERAGE(C3:Q3)
=AVERAGE(C9:Q9)
=AVERAGE(C15:Q15)

This repeats for a while, but every time I try to autofill the rest of the cells with this formula, it never continues the pattern of skipping rows, and instead fills in rows between the gaps. How can I make the autofill follow the pattern I need it to? Is it possible?

0

2 Answers 2

1

Try calculating those averages with sumif() and countif(), and take only every sixth row with query(), like this:

=arrayformula( 
  query( 
    iferror( 
      sumif( if(column(C3:Q), row(C3:Q)), row(C3:Q), C3:Q ) 
      / 
      (columns(C3:Q) - countif(C3:Q & "→" & row(C3:Q), "→" & row(C3:Q))) 
    ), 
    "skipping 6", 0 
  ) 
)
1

@doubleunary's answer is excellent and provides a single arrayformula to return all your values.

can I make the autofill follow the pattern I need it to?

Yes you can

The following formula can be auto-filled:

=IF(MOD(ROW()-3,6)=0,
   AVERAGE(C3:Q3), IFERROR(1/0)) 

Explanation

In order to work as you wish, the formula needs a way to perform one behavior on certain rows, and a different behavior on all others.

You can leverage the row numbers where you want the AVERAGE function applied because they follow a pattern.

They are all divisible by 6 without remainder after subtracting 3. For example if you subtract 3 from the row numbers 3, 9, 15, 21, you are left with 0, 6, 12, 18

Alternatively, if you subtract 3 from other row numbers like 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, you are left with 1, 2, 3, 4, 5, 7, 8, 9, 10, 11.

MOD Function

The MOD function is ideally suited as its purpose is to return the remainder of a dividend and its divisor.
MOD(dividend,divisor)

So MOD(ROW()-3,6) will equal zero on all the rows where you want the calculation performed.

Using MOD(ROW()-3,6)=0 as the test in an IF function will allow you to return your formula AVERAGE(C3:Q3) where it is TRUE or nothing if it is an in-between row and FALSE.

You could use a pair of double-quotes "" for nothing but in Sheets IFERROR(1/0) is the way to properly return "nothing"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.