Google Spreadsheet calculate change in a value over time

Question:

( by Jamethiel Bane )

Hi everyone,
I'm looking to calculate change in a value over time. A measurement is taken every day, and the date and measurement recorded. I'm looking to calculate the change in the measurement over a week. I've managed to return the most recent measurement by the following formula

=(FILTER( 'Sheet1'!C:C ; ROW('Sheet1'!C:C) =MAX( FILTER( ROW('Sheet1'!C:C) ; NOT(ISBLANK('Sheet1'!C:C))))))

But I need to subtract the value 7 rows above (which happens to be a week prior) and I can't work out how to return that. The data is in the form

11-Jun	188.5
12-Jun	187.4
13-Jun	187.8
14-Jun	186.9
15-Jun	187.4
16-Jun	186.5
17-Jun	186.5
18-Jun	186.1

Solution:

Have a look at the following screenshot:

I have the following formula in Cell E10:
=query(A1:C;"select C order by B desc limit 1")

In Cell E11:
=query(A1:C;"select C order by B desc limit 1 offset 7")

In Cell E12:
=round(query(A1:C;"select C order by B desc limit 1 offset 7")-query(A1:C;"select C order by B desc limit 1");2)



I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive