Normal Distribution chart - how to get the population numbers right

Gareth1

New member
Joined
Oct 31, 2015
Messages
4
Hello!


I've created a table in excel that uses a normal distribution formula to apportion a total income value into income bands, and charted the income in each band as well as the number of individuals in each band. I've attached the excel file and explain the issue below.


The taxpayer numbers column G (highlighted yellow) should sum to 19,732,000. This only happens when the income in each band is divided with the average income for the whole population in cell C4 (31,636).


If I assume the average income in each band is halfway between each income band value (as given to the left of "Taxpayer numbers") and divide the Income in each band (column D) by the by each band's average income (column F), that should produce the correct taxpayer numbers for column G. The problem with that is that the taxpayer numbers don't then add up to 19,732, so an adjustment factor needs to be applied to the population average (31,636) for the average of each income band so that the taxpayer numbers for each band sum to 19,732,000.


Thanks for taking a look at this. I'm at a complete loss!

Gareth:confused:
 

Attachments

  • ND Table.zip
    14.8 KB · Views: 0
Hello!


I've created a table in excel that uses a normal distribution formula to apportion a total income value into income bands, and charted the income in each band as well as the number of individuals in each band. I've attached the excel file and explain the issue below.


The taxpayer numbers column G (highlighted yellow) should sum to 19,732,000. This only happens when the income in each band is divided with the average income for the whole population in cell C4 (31,636).


If I assume the average income in each band is halfway between each income band value (as given to the left of "Taxpayer numbers") and divide the Income in each band (column D) by the by each band's average income (column F), that should produce the correct taxpayer numbers for column G. The problem with that is that the taxpayer numbers don't then add up to 19,732, so an adjustment factor needs to be applied to the population average (31,636) for the average of each income band so that the taxpayer numbers for each band sum to 19,732,000.


Thanks for taking a look at this. I'm at a complete loss!

Gareth:confused:
The way I would start is to use the lowest income level for each band and see what that does. Then I might use the median rather than the average income level or maybe even the first quartile.
 
Last edited:
The way I would start is to use the lowest income level for each band and see what that does. Then I might use the median rather than the average income level or maybe even the first quartile.
Hi Ishuda,

Thanks for your comment. The spreadsheet already uses the lowest income in each band to calculate the number of individuals in the band. I'm not sure there is anyway to calculate the median for each band either. The Normal distribution's primary purpose is to allocate the income into the various bands in the spreadsheet. That works. The problem here is working out the number of taxpayers in each band so that they all add up to the original total.

Any other suggestions?
 
Hello!


I've created a table in excel that uses a normal distribution formula to apportion a total income value into income bands, and charted the income in each band as well as the number of individuals in each band. I've attached the excel file and explain the issue below.


The taxpayer numbers column G (highlighted yellow) should sum to 19,732,000. This only happens when the income in each band is divided with the average income for the whole population in cell C4 (31,636).


If I assume the average income in each band is halfway between each income band value (as given to the left of "Taxpayer numbers") and divide the Income in each band (column D) by the by each band's average income (column F), that should produce the correct taxpayer numbers for column G. The problem with that is that the taxpayer numbers don't then add up to 19,732, so an adjustment factor needs to be applied to the population average (31,636) for the average of each income band so that the taxpayer numbers for each band sum to 19,732,000.


Thanks for taking a look at this. I'm at a complete loss!

Gareth:confused:

Why would you expect the population average to be the same as each bands average?
 
Why would you expect the population average to be the same as each bands average?

I think you may have misunderstood me as the average (mean) for the whole population would obviously be different from the mean of each band.
I have managed to solve the problem, the cause of which I still don't fully understand.
For your interest I've attached excel sheet which now does what I wanted it to.
Thank you for your comments.
 

Attachments

  • ND Table (2).zip
    15.6 KB · Views: 0
Top