Formula on the same column

[daveystokhof]

I have an excel sheet where we write down the costs of food shopping and want to see the difference between the last two months, even after adding a new month

A = person that shops
B = amount for January
C = amount for February
D = Difference

In the D column I will add the formula =sum(B2-C2) This will give me the difference between those months. But what if I want to add a new month (March) What would be the best way to see the difference between those months without changing the formula?

I tried to do it the other way
A = person that shops
B = difference
C = amount for February
D = Amount for January

with the formula in B =sum($B$2-$C$2) which works but when I add a new column between B and C for the month March... the formula will automatically change to =sum($C$2-$D$2).

I hope I made myself a bit clear ... How can I keep the formula the same?

[NBVC]

Try:

=sum(B2-INDEX(2:2,3) )

This always subtract C2 from B2... unless you insert before B2, then it becomes C2.. and you will need to adjust the 3 to column number...

[daveystokhof]

Thanks for the fast reply... but I can't... what am I doing wrong
Screen Shot 2012-06-23 at 1.29.58 AM.png

sorry I'm not very good at using Excel yet

[daveystokhof]

I'm looking around on the web the whole time... Am I the only one who is trying to do this?

[NBVC]

The formula assumed you had set up based on the first post you posted.... so formula would be in column D.

If you want to go with formula in column B...then.

=C2-INDEX(2:2,4)

The SUM() is actually redundant for this...

[daveystokhof]

It works.. but the problem is I want to insert a new column between B and C.. and when I do that. the formula will change from C2 to D2..

[NBVC]

So are talking about the setup in your attachment or in the first post?

After you insert a column what exactly do you want to calculate? Is it always the newly inserted column minus the March column (currently column D in the attachment)?

[daveystokhof]

I just added the Excel file..

Bit different but same meaning.


I count the difference(G) between 5/30/16(F) and 4/27/16(E) that.

I want to add a new column between Column F and G and add 6/28/12 in it. The difference column had to calculate the difference between June and May... Then the month after that I want to add another column and add July in it so that I can count the difference between July and June...

Basically I want to count the diference between the current and the month before that without having to change the formula over and over again.

Once again thank you for your help, I really appreciate it and I apologize for maybe not being clear enough.

[Cutter]

Simplest way would be to insert a new column immediately left of the results column. The results would use INDEX() functions that included that 'extra' column. Then, when inserting new monthly columns, you insert to the left of that extra column.
Use this formula in H2 (after inserting that new 'extra' column):

=INDEX(D2:G2,COUNT(D2:G2))-INDEX(D2:G2,COUNT(D2:G2)-1) and drag down

[daveystokhof]

Hi Cutter!

That's awesome, thanks a lot for that.

@NBVC
Thanks for the help and my apologizes for being unclear.

[Cutter]

You're welcome. Please remember to mark your thread as SOLVED (instructions in rule #9 - click Forum Rules @ top of page to view).