R1C1 Formula in Excel

[Guest]

I have an RC type formula and I want to dynamically change the end value
based on an entry in A2. I'd prefer to keep IF statements out of it if
possible.

If A2=1, I want it to be
=SUM(RC[9]:RC[9])
If A2=2 I want
=SUM(RC[9]:RC[10])
If A2=3, I want
=SUM(RC[9]:RC[11])

Suggestions?

Thanks,
Barb Reinhardt

 

[Guest]

Try =SUM(OFFSET(RC[9],0,0,1,R1C2)). The offset basically defines a range
that begins at RC[9], taking one row and R1C2 columns.

 

[Guest]

you can use the INDIRECT function which turns a text string into a reference.
=SUM(INDIRECT("RC[9]:RC["&8+A2&"]"))