Calculating rolling annual value

Hi all,

I have a spreadsheet with which I monitor my domestic electricity usage, and would like to add a column which displays my rolling annual consumption.

So, assuming column A is the date, and B is a meter reading, I'm trying to find a formula that is, in effect, If there is a date in column A that is 1 year prior to the date in the current row, take the data from B of that row and subtract from B of this row.

Any hints, tips or thoughts gratefully received!

Cheers,

Pete.

with dates in COL A (starting in A1)
readings in B (starting in B1)
in C1 put

Please Login or Register  to view this content.

and drag down
or if you dont want to see #N/A

Please Login or Register  to view this content.

Hi Martin, thanks very much for the reply. Unfortunately your solution doesn't work as written since (in the interests of simplicity) the layout of my spreadsheet doesn't match the layout I gave above. (I reasoned that it would be simple to amend any suggested solutions to fit my layout, but I failed to consider the complexity of the Index function !).

But at least you've shown me how this can be achieved, and I'm currently reading up on Index and Match functions. As I'm sure you'll appreciate, for an excel novice like myself this could take some time! I'm currently puzzling over the $B$11 parameter to Index...

If you care to take pity on me and amend your solution to fit the following layout it would help me to understand how this works...

Dates in COL A starting A4
Readings in C, starting at C4,
Formula entered in L4, and dragged down.

Thanks,
Pete.