Select closest date to a month ago

[m9m9m9m]

Hi all,

Any idea how I can select the closest date to a 30 days ago ? Vlookup & Co only deliver the largest date that is smaller then the selection, not necessarily the closest. Here is an example:

Col A Col B Col C
"DATE" "DATA" "Last 30 day Average of Data"
25/10/2009 5 "formula to calculate the average of the last 30 days"<formula to="" calculate="" average="" of="" last="" 30="" days="" until="" date="">
26/11/2012 6 <formula to="" calculate="" average="" of="" last="" 30="" days="" until="" date="">"formula to calculate the average of the last 30 days"
25/12/2012 7 <formula to="" calculate="" average="" of="" last="" 30="" days="" until="" date="">"formula to calculate the average of the last 30 days"

Match and Vlookup in C5 will look at the 2009 date but of course 26/11/2012 is much closer to -30 days than the 2009 date.
I have written a macro to solve this - but I'd like a formula in Col C. It should not be an array formula as this formula has to work on about 300 rows in Col C.
I found this - which is an array, hence not tested:

=INDEX(Datelist, MATCH(MIN(ABS(Datelist-A1)), ABS(Datelist-A1), 0))

The date list is sorted by date.
Any help would be very much appreciated.</formula></formula></formula>

 

[m9m9m9m]

fyi: The Macro basically goes back to find a date that is smaller that A5-30days then checks if the next entry is closer to 30 days (i.e. chooses the smallest value for date-30 from the 2 candidates)

 

[Vidar]

Not sure if I understand the question, but this one calculates the average of the last 30 days
based on the date in A column.
=AVERAGEIFS($C$2:$C$100,$B$2:$B$100,"<="&B2,$B$2:$B$100,">"&(B2-30))
Change the range to your needs. Paste it in C2 and copy down.

Vidar

 

[Robert Mika]

Your Dates A2:a24 on Sheet3
In E1:
=TODAY()-30
Named Range:
=ABS(Sheet3!$A$2:$A$24-Sheet3!$E$1)=MIN(ABS(Sheet3!$C$2:$C$24-Sheet3!$E$1))
Formula
=INDEX($A$2:$A$24,MATCH(TRUE,dates,ROWS($1:1)-1))

 

[m9m9m9m]

Thanks Vidar and Robert for your quick response.
Unfortunately none of the suggested methods worked for me, although I didn't quite understand your method Robert.

Here is the workaround I have at the moment, which does not really work as it chooses the wrong line (hope this copy and paste works):

cell A1	Date	Reading	Average of last 30 days based on current reading	Comment
cell A2	15-Feb 00:00	12447.0000	#N/A
cell A3	10-Apr 09:47	12522.7485	41.70
cell A4	18-Apr 12:09	12533.1428	41.37
cell A5	18-May 12:30	12565.6894	33.07
cell A6	27-Jun 08:44	12597.9160	24.67
cell A7	21-Jul 21:53	12616.8650	24.24
cell A8	26-Jul 21:53	12626.8650	26.89	this row is currently using the 18-May data but should be using the 27-June Data since 27-June is closer to "a month ago" than 18-May
cell A9
cell A10	formula in D8: '=(C8-VLOOKUP(B8-30,B$2:C8,2))/(B8-VLOOKUP(B8-30,B$2:C8,1))*30.5
cell A11	simplified formula in D8: =(C8-C5)/(B8-B5)*30.5

<tbody>
</tbody>

 

[m9m9m9m]

cell A12

simplified should be formula in D8: '=(C8-C6)/(B8-B6)*30.5

<tbody>
</tbody>

I hope this makes a little more sense of what I am trying to achieve.