Adding Value to Cell Reference

[iamnu]

The following formula is in Cell A1
=AVERAGE(Sheet1!F14:F20)

When I COPY this formula to Cell A2, I would like to be able to add a
value to specify which row in Sheet1 I want the formula to refer to.

For example, if I copy the formula to the next cell, I'd like to be
able to use a variable, say B1 (where B1 =3) to make the copied
formula look like this (adding B1 value to F14 and F20 references to
get):
=AVERAGE(Sheet1!F17:F23)

Can someone explain how?

 

[T. Valko]

Why don't you just use the actual row number?

B1 = 17

=AVERAGE(INDEX(Sheet1!F:F,B1):INDEX(Sheet1!F:F,B1+6))

Which translates to:

=AVERAGE(Sheet1!F17:F23)

 

[iamnu]

Thank you, sir!

Why don't you just use the actual row number?

B1 = 17

=AVERAGE(INDEX(Sheet1!F:F,B1):INDEX(Sheet1!F:F,B1+6))

Which translates to:

=AVERAGE(Sheet1!F17:F23)

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thank you, sir!