Adding Value to Cell Reference

  • Thread starter Thread starter iamnu
  • Start date Start date
I

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?
 
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)
 
Thank you, sir!

T. Valko said:
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)
Click to expand...
 
Back
Top