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):

[TABLE="width: 740"]
<tbody>[TR]
[TD]cell A1[/TD]
[TD]Date[/TD]
[TD]Reading[/TD]
[TD]Average of last 30 days based on current reading[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]cell A2[/TD]
[TD="align: right"]15-Feb 00:00[/TD]
[TD="align: right"]12447.0000[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cell A3[/TD]
[TD="align: right"]10-Apr 09:47[/TD]
[TD="align: right"]12522.7485[/TD]
[TD]41.70[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cell A4[/TD]
[TD="align: right"]18-Apr 12:09[/TD]
[TD="align: right"]12533.1428[/TD]
[TD]41.37[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cell A5[/TD]
[TD="align: right"]18-May 12:30[/TD]
[TD="align: right"]12565.6894[/TD]
[TD]33.07[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cell A6[/TD]
[TD="align: right"]27-Jun 08:44[/TD]
[TD="align: right"]12597.9160[/TD]
[TD]24.67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cell A7[/TD]
[TD="align: right"]21-Jul 21:53[/TD]
[TD="align: right"]12616.8650[/TD]
[TD]24.24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cell A8[/TD]
[TD="align: right"]26-Jul 21:53[/TD]
[TD="align: right"]12626.8650[/TD]
[TD]26.89[/TD]
[TD]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[/TD]
[/TR]
[TR]
[TD]cell A9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cell A10[/TD]
[TD="colspan: 4"]formula in D8: '=(C8-VLOOKUP(B8-30,B$2:C8,2))/(B8-VLOOKUP(B8-30,B$2:C8,1))*30.5[/TD]
[/TR]
[TR]
[TD]cell A11[/TD]
[TD="colspan: 3"]simplified formula in D8: =(C8-C5)/(B8-B5)*30.5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[m9m9m9m]

[TABLE="width: 131"]
<tbody>[TR]
[TD="width: 56"]cell A12[/TD]
[TD="width: 75"]simplified should be formula in D8: '=(C8-C6)/(B8-B6)*30.5[/TD]
[/TR]
</tbody>[/TABLE]

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