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 10day period for 242 10day periods? Use the And & If statements to find the
median of the number of winning days over a 10day period.
VDE UpDay 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 UpDay 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 K1 is 0 and CDF of K is 1. The
median is 5 winning days over the sample of 242 10day 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