Finding change in value over specified time.

[deeetour]

Hey guys,


I have a friend who has been tracking their weight loss in a spreadsheet, and asked me to do some visualisations on it. One thing I'd like to be able to do is report the total weight lost for a given period. Is there an easy way to do this via formula?


For example, I have two columns, DATE, WEIGHT, and about a year's worth of data. I'd like to be able to say "Total Weight Lost in July 2012 was 8kg" - so I need to subtract the weight value of the last day of the month from the weight value of the first day of the month. I can't use MIN/MAX because they may lose or gain weight throughout the month, but I need the Net change.


Thanks!

 

[Weow]

You just need to locate the first and last date of the month and then subtract the last day's weight from the first day's. To get the first day of the month's weight use:


INDEX(weight_range,MATCH(DATEVALUE("07/01/12"),date_range,0))


Then, to get the last day of the month's weight, use:


INDEX(weight_range,MATCH(EOMONTH(DATEVALUE("07/01/12"),0),date_range,0))


Im sure there are even better ways to do it, but this way is simple. It will only work if there is an entry for the first and last day of July, though.