VBA function find closest date from list

[gero92]

Hello everybody,

I am writing a vba function that assigns a value of 10 * a probability to each calendar date in the future, only if that date is within -5<date<5 the next closest date from the EVENTS list (on the left below the excel example). So if I am on the 17/04/2018 and the closest EVENTS date is the 18/04/2018, the function will assign a value of 5 (10*EVENTS probability). The 15/05/2018 will have a value of 6 ( closest EVENT 18/05/2018, prob 60%). A date like 25/04/2018 will have a value of 0 as it is not within 5 days of any EVENTS date. I can't figure out how to use the correct probability in the VBA function. Below what I wrote. I am an absolute beginner of VBA but I am learning so much from this forum. Any very simple solution to this would be soon much appreciated guys!

Function IncreaseT(t As Integer, prob As Range)
' t is the calendar date
' prob is the probability assigned to each EVENTS date


If t > 5 Or t < -5 Then
IncreaseT = 0
Else: IncreaseT = 10 * prob
Exit Function


[TABLE="width: 536"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD]EVENTS[/TD]
[TD]Probability[/TD]
[TD][/TD]
[TD]Calendar[/TD]
[TD]Distance from closest Event[/TD]
[/TR]
[TR]
[TD]18/04/18[/TD]
[TD]50%[/TD]
[TD][/TD]
[TD]15/04/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18/05/18[/TD]
[TD]60%[/TD]
[TD][/TD]
[TD]16/04/18[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]20/08/18[/TD]
[TD]50%[/TD]
[TD][/TD]
[TD]17/04/18[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]02/11/18[/TD]
[TD]80%[/TD]
[TD][/TD]
[TD]18/04/18[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]24/02/19[/TD]
[TD]20%[/TD]
[TD][/TD]
[TD]19/04/18[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]29/05/19[/TD]
[TD]30%[/TD]
[TD][/TD]
[TD]20/04/18[/TD]
[TD]-2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]21/04/18[/TD]
[TD]-3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22/04/18[/TD]
[TD]-4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]23/04/18[/TD]
[TD]-5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]24/04/18[/TD]
[TD]-6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]25/04/18[/TD]
[TD]-7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]26/04/18[/TD]
[TD]-8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]27/04/18[/TD]
[TD]-9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]28/04/18[/TD]
[TD]-10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]29/04/18[/TD]
[TD]-11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30/04/18[/TD]
[TD]-12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01/05/18[/TD]
[TD]-13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]02/05/18[/TD]
[TD]-14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]03/05/18[/TD]
[TD]-15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04/05/18[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]05/05/18[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]06/05/18[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]07/05/18[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08/05/18[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]09/05/18[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10/05/18[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11/05/18[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12/05/18[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13/05/18[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14/05/18[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15/05/18[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]16/05/18[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17/05/18[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]18/05/18[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]19/05/18[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20/05/18[/TD]
[TD]-2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]21/05/18[/TD]
[TD]-3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22/05/18[/TD]
[TD]-4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]23/05/18[/TD]
[TD]-5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]24/05/18[/TD]
[TD]-6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]25/05/18[/TD]
[TD]-7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]26/05/18[/TD]
[TD]-8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]27/05/18[/TD]
[TD]-9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]28/05/18[/TD]
[TD]-10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]29/05/18[/TD]
[TD]-11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30/05/18[/TD]
[TD]-12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]31/05/18[/TD]
[TD]-13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01/06/18[/TD]
[TD]-14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]02/06/18[/TD]
[TD]-15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]03/06/18[/TD]
[TD]-16[/TD]
[/TR]
</tbody>[/TABLE]

 

[gero92]

And thank you so much in advance guys this forum is just awesome!

 

[gero92]

The initial explanation was cut as too long I suppose. The thing is that the probability in the vba function has to be the one assigned in the excel to each date in the list. So for the calendar date ex. 15/05/2018 I want the prob to be 60% (closest to 18/05/2018 date in EVENT list), while for let's say the 25/08/2018 the prob will be 50% (closest the third date in EVENT). The t in the vba function is the distance of each calendar date to the closest date in the EVENT list from excel. Hope it is clearer now! Anybody who could help please?

 

[shg]

How about just a formula?

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

EVENTS​

[/td][td="bgcolor:#F3F3F3"]

Probability​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

Calendar​

[/td][td="bgcolor:#F3F3F3"]

Prob​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

18 Apr 2018​

[/td][td]

50%​

[/td][td][/td][td]

15 Apr 2018​

[/td][td="bgcolor:#CCFFCC"]

50%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

18 May 2018​

[/td][td]

60%​

[/td][td][/td][td]

16 Apr 2018​

[/td][td="bgcolor:#CCFFCC"]

50%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

20 Aug 2018​

[/td][td]

50%​

[/td][td][/td][td]

17 Apr 2018​

[/td][td="bgcolor:#CCFFCC"]

50%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

02 Nov 2018​

[/td][td]

80%​

[/td][td][/td][td]

18 Apr 2018​

[/td][td="bgcolor:#CCFFCC"]

50%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

24 Feb 2019​

[/td][td]

20%​

[/td][td][/td][td]

19 Apr 2018​

[/td][td="bgcolor:#CCFFCC"]

50%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

29 May 2019​

[/td][td]

30%​

[/td][td][/td][td]

20 Apr 2018​

[/td][td="bgcolor:#CCFFCC"]

50%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td][/td][td][/td][td][/td][td]

21 Apr 2018​

[/td][td="bgcolor:#CCFFCC"]

50%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td][/td][td][/td][td][/td][td]

22 Apr 2018​

[/td][td="bgcolor:#CCFFCC"]

50%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td][/td][td][/td][td][/td][td]

23 Apr 2018​

[/td][td="bgcolor:#CCFFCC"]

50%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td][/td][td][/td][td][/td][td]

24 Apr 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td][/td][td][/td][td][/td][td]

25 Apr 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td][/td][td][/td][td][/td][td]

26 Apr 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td][/td][td][/td][td][/td][td]

27 Apr 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td][/td][td][/td][td][/td][td]

28 Apr 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td][/td][td][/td][td][/td][td]

29 Apr 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td][/td][td][/td][td][/td][td]

30 Apr 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td][/td][td][/td][td][/td][td]

01 May 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td][/td][td][/td][td][/td][td]

02 May 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td][/td][td][/td][td][/td][td]

03 May 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td][/td][td][/td][td][/td][td]

04 May 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td][/td][td][/td][td][/td][td]

05 May 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td][/td][td][/td][td][/td][td]

06 May 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td][/td][td][/td][td][/td][td]

07 May 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

25​

[/td][td][/td][td][/td][td][/td][td]

08 May 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

26​

[/td][td][/td][td][/td][td][/td][td]

09 May 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

27​

[/td][td][/td][td][/td][td][/td][td]

10 May 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

28​

[/td][td][/td][td][/td][td][/td][td]

11 May 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

29​

[/td][td][/td][td][/td][td][/td][td]

12 May 2018​

[/td][td="bgcolor:#CCFFCC"]

0%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

30​

[/td][td][/td][td][/td][td][/td][td]

13 May 2018​

[/td][td="bgcolor:#CCFFCC"]

60%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

31​

[/td][td][/td][td][/td][td][/td][td]

14 May 2018​

[/td][td="bgcolor:#CCFFCC"]

60%​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

32​

[/td][td][/td][td][/td][td][/td][td]

15 May 2018​

[/td][td="bgcolor:#CCFFCC"]

60%​

[/td][/tr]
[/table]

The array formula in E2 and copied down is

=IF(ABS(INDEX($A$2:$A$7, MATCH(MIN(ABS($A$2:$A$7 - D2)), ABS($A$2:$A$7 - D2), 0)) - D2) > 5, 0, 
        INDEX($B$2:$B$7, MATCH(MIN(ABS($A$2:$A$7 - D2)), ABS($A$2:$A$7 - D2), 0)))

 

[gero92]

Worked thank you so much!!