 # Excel

I have numbers in columns a, b & c.  In column d I have a formula that adds up the three numbers to the left.  I need to be able to insert a blank column at column D, insert a new number into column D and have the formula automatically adjust to add the three numbers to the left which will now be columns b, c & d.  Is there any way to do that?

9 Replies

# Re: Excel

In D2:

``=SUM(OFFSET(D2,0,-3,1,3))``

or

``=SUM(INDEX(2:2,COLUMN()-3):INDEX(2:2,COLUMN()-1))``

Fill down.

# Re: Excel

It worked!  Thank you thank you thank you!!!

# Re: Excel

One follow-up question - how would I write the formula if I want to do A1-A2+A3

# Re: Excel

In which cell do you want the formula? In A4?

# Re: Excel

No, in cell D1. So D1 adds A1, subtracts, b1, then adds C1. We need to be able to insert a blank column at D and have the formula which now appears in E1 automatically adjusted to add B1, subtract C2 and Add the new entry in D1. We'll be doing this every month always analyzing the last three months.

# Re: Excel

@Michael1957

I'm confused. You first mentioned A1-A2+A3, then A1-B1+C1 and finally something with B1 and C2.

# Re: Excel

Sorry Hans - I've been at this all day so I'm a bit fuzzy. It's A1-B1+C1 with the formula appearing in D1. We then insert a blank column at D and want the formula to automatically adjust to B1-C1+D1. The formula you first mentioned worked but only if adding all three cells. I'm not sure how to do it if I want to include subtracting a cell in the formula.

# Re: Excel

Thanks. That would be

``=INDEX(2:2,COLUMN()-3)-INDEX(2:2,COLUMN()-2)+INDEX(2:2,COLUMN()-1)``

# Re: Excel

Wow! That's some formula, but definitely doable. Thank you so much Hans! Much appreciated.