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