CODEWITHSUNDEEP
excelexcel-formula
Dan Is Fiddling By Firelight
Dan Is Fiddling By Firelight

Reputation: 6061

Find year ago value in unevenly ordered excel spreadsheet

I have a spreadsheet containing date changed, new value pairs. To look at how the data is changing over time I'd like to have a formula to get the value from a year ago. If the samples happened at a consistent rate it would be easy because I could just look up N cells. They're not though, so I need a formula that would search the date column for the last date more than a year in the past and return the value

Below is a sample of what my spreadsheet data looks like (first two columns) and what I would like a formula to compute (third column). 

Date        Value   Value a year ago
1/5/2013    505
1/29/2013   502
2/3/2013    488
3/2/2013    491
3/5/2013    503
5/3/2013    511
5/8/2013    509
5/11/2013   510
…   
1/22/2014   488     505
1/30/2014   501     502
2/3/2014    503     488

Upvotes: 1

Views: 229

Answers (1)

teylyn
teylyn

Reputation: 35970

Try in cell C11 and copied down

=VLOOKUP(EDATE(A11,-12),A:B,2,1)

Edate() will increase a date by the specified number of months. Negative values subtract months, so -12 will subtract 12 months.

Vlookup with the 1 as the last parameter will return the first value that is equal to or less than the lookup value.

The data must be sorted ascending by the first column for this to work.

Formula applied in D11 and copied down in the screenshot

enter image description here

Upvotes: 4

Related Questions