SOLVED

Excel

Copper Contributor

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
best response confirmed by allyreckerman (Microsoft)
Solution

@Michael1957 

In D2:

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

or

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

Fill down.

@Hans Vogelaar 

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

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

@Michael1957 

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

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.

@Michael1957

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

Please explain.

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.

@Michael1957 

Thanks. That would be

=INDEX(2:2,COLUMN()-3)-INDEX(2:2,COLUMN()-2)+INDEX(2:2,COLUMN()-1)
Wow! That's some formula, but definitely doable. Thank you so much Hans! Much appreciated.
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Michael1957 

In D2:

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

or

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

Fill down.

View solution in original post