Lognormal distribution of subjects by income XL5A-V0G 26 Mar 2017 - - PDF document

Lognormal distribution of subjects by income XL5A-V0G 26 Mar 2017 2014-Schield-LogNormal-Income1-Excel2013-Slides.pdf 1

XL5A: 0G 1

Milo Schield

Augsburg College Editor: www.StatLit.org US Rep: International Statistical Literacy Project

Output, slides and data at www.StatLit.org/ pdf/2014-Schield-LogNormal-Income1-Excel2013-Demo.pdf pdf/2014-Schield-LogNormal-Income1-Excel2013-Slides.pdf XLS/2014-Schield-LogNormal-Income1-Excel2013-Data.xlsx

Lognormal Distribution

• f Subjects by Income

XL5A: 0G 2

The log of a Normal distribution is not symmetric. It is never negative and it typically has a long right tail. Some things are distributed log-normally:

• People by income, assets, weight and blood pressure
• Cities by population; insurance claims by amount

ASSIGNMENT: 1) Create the table shown on slide 4 (See demo output) 2) Create the graph shown on slide 5. Upload results. 3) Review the questions shown on slide 6.

Log-Normal Distributions

XL5A: 0G 3

Enter 50 (median income) and 80 (average income) in B4 and B5. Enter formulas in G2:G5. Enter formulas in B9 & C9. Check values against demo output.

Enter data and formulas for top section

XL5A: 0G 4

2) Select B14:E14. Pull down to bottom row: row 28. Check results against demo output. Note: If you copy D9 into C14, column C will give wrong values. Copy C9 into C14.

1) Copy C9; Paste into C14. Enter formula in B14, D14, E14.

XL5A: 0G 5

.

Create this graph. Data: Col B, D & E; Rows 13-28

0% 25% 50% 75% 100% 50 100 150 200 250 300 350 400 450 500 Household Incomes ($1,000)

Log-Normal: PDF and CDF Household Incomes: Mean = 80K, Median = 50K

Distribution of Households by Household Income Probability Distribution Function (PDF): Percentage of the Modal PDF Cumulative Distribution Function (CDF): Percentage of Households with Incomes below price

XL5A: 0G 6

If X is income, then CDF(X) is the percentage of subjects who have LESS than X thousand dollars in income [Cumulative Distribution Function] If median = $50K and mean = $80K, what percentage of subjects have incomes …

• a. below 10K? 4.8%.

Solution: Find 10K in column B in row 15. Find matching CDF in same row, column E,

• b. ABOVE 10K? 100% - 4.8% = 95.2%

CDF Percentages; Practice Problems a and b

2014 Schield Log-Normal Income1

XL5A: 0G 1

Milo Schield

Augsburg College Editor: www.StatLit.org US Rep: International Statistical Literacy Project

Output, slides and data at www.StatLit.org/ pdf/2014-Schield-LogNormal-Income1-Excel2013-Demo.pdf pdf/2014-Schield-LogNormal-Income1-Excel2013-Slides.pdf XLS/2014-Schield-LogNormal-Income1-Excel2013-Data.xlsx

Lognormal Distribution

• f Subjects by Income

2014 Schield Log-Normal Income1

XL5A: 0G 2

The log of a Normal distribution is not symmetric. It is never negative and it typically has a long right tail. Some things are distributed log-normally:

• People by income, assets, weight and blood pressure
• Cities by population; insurance claims by amount

ASSIGNMENT: 1) Create the table shown on slide 4 (See demo output) 2) Create the graph shown on slide 5. Upload results. 3) Review the questions shown on slide 6.

Log-Normal Distributions

2014 Schield Log-Normal Income1

XL5A: 0G 3

Enter 50 (median income) and 80 (average income) in B4 and B5. Enter formulas in G2:G5. Enter formulas in B9 & C9. Check values against demo output.

Enter data and formulas for top section

2014 Schield Log-Normal Income1

XL5A: 0G 4

2) Select B14:E14. Pull down to bottom row: row 28. Check results against demo output. Note: If you copy D9 into C14, column C will give wrong values. Copy C9 into C14.

1) Copy C9; Paste into C14. Enter formula in B14, D14, E14.

2014 Schield Log-Normal Income1

XL5A: 0G 5

.

Create this graph. Data: Col B, D & E; Rows 13-28

0% 25% 50% 75% 100% 50 100 150 200 250 300 350 400 450 500 Household Incomes ($1,000)

Log-Normal: PDF and CDF Household Incomes: Mean = 80K, Median = 50K

Distribution of Households by Household Income Probability Distribution Function (PDF): Percentage of the Modal PDF Cumulative Distribution Function (CDF): Percentage of Households with Incomes below price

2014 Schield Log-Normal Income1

XL5A: 0G 6

If X is income, then CDF(X) is the percentage of subjects who have LESS than X thousand dollars in income [Cumulative Distribution Function] If median = $50K and mean = $80K, what percentage of subjects have incomes …

• a. below 10K? 4.8%.

Solution: Find 10K in column B in row 15. Find matching CDF in same row, column E,

• b. ABOVE 10K? 100% - 4.8% = 95.2%

CDF Percentages; Practice Problems a and b