I need to sum the preceding 12 cells in a row within excel - regardless of
the fact that some columns will be added in front of this cell with the
formula. I want the formula to "float" just not sure how to accomplish it?

=SUM(INDEX(1:1,1,COLUMN()-12):INDEX(1:1,1,COLUMN()-1))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Mopechicken" wrote in message
...
I need to sum the preceding 12 cells in a row within excel - regardless of
the fact that some columns will be added in front of this cell with the
formula. I want the formula to "float" just not sure how to accomplish
it?

try
=SUM(OFFSET(G1,0,-12,1,12))

--
Don Guillett
SalesAid Software

"Mopechicken" wrote in message
...
I need to sum the preceding 12 cells in a row within excel - regardless of
the fact that some columns will be added in front of this cell with the
formula. I want the formula to "float" just not sure how to accomplish
it?

the last number needs to be one MORE than the columns back
=SUM(OFFSET(G1,0,-3,1,4))

--
Don Guillett
SalesAid Software

"Mopechicken" wrote in message
...
I need to sum the preceding 12 cells in a row within excel - regardless of
the fact that some columns will be added in front of this cell with the
formula. I want the formula to "float" just not sure how to accomplish
it?

A bit more robust in case there aren't 12 cells before

=SUM(INDEX(1:1,1,MAX(1,COLUMN()-12)):INDEX(1:1,1,MAX(1,COLUMN()-1)))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" wrote in message
...
=SUM(INDEX(1:1,1,COLUMN()-12):INDEX(1:1,1,COLUMN()-1))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Mopechicken" wrote in message
...
I need to sum the preceding 12 cells in a row within excel - regardless
of
the fact that some columns will be added in front of this cell with the
formula. I want the formula to "float" just not sure how to accomplish
it?

Bob - this is perfect!
Thanks so much!!!
Not quite sure what the formula is saying - but it works perfectly!!!

"Bob Phillips" wrote:

=SUM(INDEX(1:1,1,COLUMN()-12):INDEX(1:1,1,COLUMN()-1))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Mopechicken" wrote in message
...
I need to sum the preceding 12 cells in a row within excel - regardless of
the fact that some columns will be added in front of this cell with the
formula. I want the formula to "float" just not sure how to accomplish
it?

I posted a more robust version just after, in case you start before column M
say.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mopechicken" wrote in message
...
Bob - this is perfect!
Thanks so much!!!
Not quite sure what the formula is saying - but it works perfectly!!!

"Bob Phillips" wrote:

=SUM(INDEX(1:1,1,COLUMN()-12):INDEX(1:1,1,COLUMN()-1))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Mopechicken" wrote in message
...
I need to sum the preceding 12 cells in a row within excel -
regardless of
the fact that some columns will be added in front of this cell with
the
formula. I want the formula to "float" just not sure how to
accomplish
it?