can a1 reference style and r1c1 style be used in same formula?

[Guest]

I need to use a formula like sum(c1.c5)/r[4]c[-1].

Since as and when I add any rows between row 1 & 5 the formula should
automatically change to sum(c1.c6) but the cell as a divison should always be
the value in the cell 4 rows down in B column.

 

[Leo Heuser]

I need to use a formula like sum(c1.c5)/r[4]c[-1].

Since as and when I add any rows between row 1 & 5 the formula should
automatically change to sum(c1.c6) but the cell as a divison should always
be
the value in the cell 4 rows down in B column.

rjagga

This formula will do the job:

=SUM($C$1:$C$5)/OFFSET($C$1:$C$5,(ROWS($C$1:$C$5)-1)+4,-1,1,1)

 

[Leo Heuser]

I need to use a formula like sum(c1.c5)/r[4]c[-1].

Since as and when I add any rows between row 1 & 5 the formula should
automatically change to sum(c1.c6) but the cell as a divison should always
be
the value in the cell 4 rows down in B column.

rjagga

This formula will do the job:

=SUM($C$1:$C$5)/OFFSET($C$1:$C$5,(ROWS($C$1:$C$5)-1)+4,-1,1,1)