formula to sum the prior 12 cells regardless of added columns?
 

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?

formula to sum the prior 12 cells regardless of added columns?
 

=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?

formula to sum the prior 12 cells regardless of added columns?
 

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?

formula to sum the prior 12 cells regardless of added columns?
 

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?

formula to sum the prior 12 cells regardless of added columns?
 

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?

formula to sum the prior 12 cells regardless of added columns?
 

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?

formula to sum the prior 12 cells regardless of added columns?
 

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?