Monday, August 20, 2018

The And & If Statements


Using And & If Statements to find the median of a pdf

Question:   The table below has data on number of days a stock fund rises in a 10-day period for 242 10-day periods?  Use the And & If statements to find the median of the number of winning days over a 10-day period.



VDE Up-Day Frequency  for 10 day period
X=k
Freq K
P(X=k)
CDF(k)
0
0
0.000
0.000
1
0
0.000
0.000
2
3
0.012
0.012
3
24
0.099
0.112
4
54
0.223
0.335
5
48
0.198
0.533
6
55
0.227
0.760
7
26
0.107
0.868
8
25
0.103
0.971
9
7
0.029
1.000
10
0
0.000
1.000
242
1.000


Comment:  I routinely look at statistics like this for many stocks and I need to modify my spreadsheets so this calculation is updated automatically when I add new data.

Code:   The data (x=0 to x=k)  is in cells range a3:d10.  The CDF in column D is the input.  I place code in column E.


CDF(k)
Col E.
0.000
=IF(D3>=0.5,1,0)
0.000
=IF(AND(D3<0.5,D4>=0.5),1,0)
0.012
=IF(AND(D4<0.5,D5>=0.5),1,0)
0.112
=IF(AND(D5<0.5,D6>=0.5),1,0)
0.335
=IF(AND(D6<0.5,D7>=0.5),1,0)
0.533
=IF(AND(D7<0.5,D8>=0.5),1,0)
0.760
=IF(AND(D8<0.5,D9>=0.5),1,0)
0.868
=IF(AND(D9<0.5,D10>=0.5),1,0)
0.971
=IF(AND(D10<0.5,D11>=0.5),1,0)
1.000
=IF(AND(D11<0.5,D12>=0.5),1,0)
1.000
=IF(AND(D12<0.5,D13>=0.5),1,0)



My final spreadsheet looks like this:


VDE Up-Day Frequency for 10 day period
X=k
Freq K
P(X=k)
CDF(k)
 Col E
0
0
0.000
0.000
0
5
1
0
0.000
0.000
0
2
3
0.012
0.012
0
3
24
0.099
0.112
0
4
54
0.223
0.335
0
5
48
0.198
0.533
1
6
55
0.227
0.760
0
7
26
0.107
0.868
0
8
25
0.103
0.971
0
9
7
0.029
1.000
0
10
0
0.000
1.000
0


The median occurs for the value k where CDF of K-1 is 0 and CDF of K is 1.     The median is 5 winning days over the sample of 242 10-day periods for this fund.


I tried to extract the value of the median using Lookup function.  It bombed.   However, since there is only one value of column E that has a value of 1 the SUMPRODUCT of col e and col a (the values of k) gives us the median.   This is in cell F3 above.

Authors Notes:

Go to the following post to learn how to use the frequency function in Excel.






No comments:

Post a Comment