Cals based on date range

Date Value 1 Value 2
1/2/2013 5 66
1/3/2013 61 57
1/4/2013 110 1
1/5/2013 73 63
1/6/2013 21 29
1/7/2013 25 64
1/8/2013 29 21
1/9/2013 33 47
1/10/2013 37 59
1/11/2013 41 28
1/12/2013 45 63
1/13/2013 49 20
1/14/2013 53 24
1/15/2013 57 44
1/16/2013 9 93
1/17/2013 13 63
1/18/2013 17 77
1/19/2013 73 29
1/20/2013 77 4
1/21/2013 81 42
1/22/2013 110 11
1/23/2013 89 15
1/24/2013 93 38
1/25/2013 98 73
1/26/2013 101 23
1/27/2013 105 37


Range 1 1/22/2013 through 1/27/2013
Range 2 1/3/2013 through 1/10/2013

Value 2 minus Value 1 for Range 1 => (37-105)*100=-6800
Value 2 minus Value 1 for Range 2 => (59-37)*100=2200

I couldn't upload the excel file with the above data but I tried to show the calculations that I want to do:

- basically I want based on the range of dates I have - for example range 1 is 1/22/2013-1/27/2013 - and from the data to get values 1 and 2 from the last date of the range, which in this case is 105 and 37 and to subtract and then multiply by 100 as shown above

- same logic for the range 2

Thanks for all your help!

I'm having trouble figuring out why the first date in the range matters, if you're only linking data to the second date.

The only reason I can imagine for using the first date in the range would be for summing all of the numbers in between them, but that doesn't seem to be what you need.

Assuming G1 = 1/27/2013
Assuming G2 = 1/10/2013

RANGE 1
=SUMPRODUCT((A2:A27=G1)*(C2:C27))-SUMPRODUCT((A2:A27=G1)*(B2:B27))

RANGE 2
=SUMPRODUCT((A2:A27=G2)*(C2:C27))-SUMPRODUCT((A2:A27=G2)*(B2:B27))

The reason why I want it to refer to a range is that ultimately I have data from 2010 and will be adding data every day. So every week I will show have to show the calculated number for the past week, 1 week before, 2 weeks before, 6 months before and 1 year before.

So lets say if given the data I have above the past week Monday was 1/22/2013 and Friday was 1/27/2013 then calculate (37-105)*100=-6800 (based of the values in 1/27/2013).

Then same calculation for 1 week prior would be if Monday was 1/18 and Friday was 1/21 the do (42-81)*100 which is based of the 1/21 values...and so on

I have a formula in a cell that calculates what date was Fri last week, 1 week ago, 2 weeks ago, 6 months ago, and 1 year ago, and from the corresponding Friday date I calculate what date was Monday so I can get between what dates I need to look to do the calculation.

I am trying to automate the process as much as I can because it is something I need to do every week so I don't want to enter by hand too much of this but rather to look up the appropriate date range and do the calculation.

Maybe there is another easier way to set it up that I cant think of. Any suggestions would be very helpful.

Thanks!

Actually I was just trying out the formula with all the different scenarios and works perfect, and I just need the end date. Thanks daffodil11!

Woo woo! Glad I could help.