Any Excel boffins out there? Help required

worktolive_2

Hi,
I hope someone can enlighten me or at least tell me if this is not possible.
I want to create a spread sheet on excel where I am logging the value of some investments over specified periods (in this case quarterly). I want there to be a column which takes the values of the last 2 valuations, then works out the difference between them and provides a +/- figures to give me the growth / loss. This in itself is not a problem, but I want the comparison column to work out the diffrerentials on a rolling basis, i.e.

July October January Value (between last 2 values)
£xxx £xxx £xxx +/-£xxx

Basically, I want the value +/- column to show the difference between the last 2 valuations, july / October, october / January, january / april etc, but I dont want to have loads of the value columns on the worksheet.
Is there a way of doing this?
Many thanks

worktolive

WTFH

Ok, a couple of thoughts...
1. Do it vertically, not horizontally, so column A is date, column B is value and column C is calculation
You then could do...
January £100
April £110 (b2-b1)
July £108 (b3-b2)
If you copy down the calculation in column C it will then show you the +/- each time.

...alternatively, give me a couple of minutes to work out a more complex solution...

WTFH

The over-complex answer...
Date in column A
Values in B
Lets say we have 15 rows plus the title row.

Somewhere in column c, d, e, doesn't matter, put this in a cell...
=(INDEX(B2:B16,COUNT(B2:B16),1)-INDEX(B2:B16,COUNT(B2:B16)-1,1))

As long as you leave the future values blank, that will calculate the difference between the two most recent transactions (up to B16). If you want to go further, change B16 everywhere above to B99, or whatever you want.

paddyrg

Post 2 would be the most universally accepted/cleanest method. I'd go with something based on that tbh.

worktolive_2

Thanks,
I'll give that a whirl and see what happens. Thanks fort taking the time to respond, appreciate it.