Reference a Cell for Row Number

[Doug]

I'm trying see the correlation of a set of numbers but would like to
change the start and end dates easily. Rather than manually changing
the formula, I'd like to have two reference cells where I can type the
beginning and end cells or row number.
Maybe a simpler explantation: is it possible to create a sum function
(say =sum(A1:A10)) however, I'd like to have two cells that the formula
references to check what rows to start and end at (If there is a 1 and
a 10, it sums A1 to A10; if there is a 3 and a 8, it sums A3 to A8)?

 

[Harlan Grove]

Doug wrote...
....

Maybe a simpler explantation: is it possible to create a sum function
(say =sum(A1:A10)) however, I'd like to have two cells that the formula
references to check what rows to start and end at (If there is a 1 and
a 10, it sums A1 to A10; if there is a 3 and a 8, it sums A3 to A8)?

C1:
3

C2:
8

C3:
=SUM(INDEX(A:A,C1):INDEX(A:A,C2))

 

[kletcho]

also:

=SUM(OFFSET(A1,C1-1,0,C2-C1,0))

 

[Harlan Grove]

(e-mail address removed) wrote...

also:

=SUM(OFFSET(A1,C1-1,0,C2-C1,0))

True, but OFFSET is a volatile function, meaning it recalcs anytime
anything else recalcs. INDEX isn't volatile, so it only recalcs when
it's source range changes.

 

[Kletcho]

Good to know. Especially if you had to use the formula in many cells.