ExcelBanter - population percentages and ranks

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - population percentages and ranks (https://www.excelbanter.com/excel-worksheet-functions/96737-population-percentages-ranks.html)

population percentages and ranks

I need help writing formulas or functions. The chart on this website is
out of date. There is newer census data, but I have to go to the library
to get it.
http://www.enchantedlearning.com/usa...pulation.shtml

Column A
State name

Column B
Ranking in numbers (copied from the chart on that web page)

Column C
A formula or function to verify the rankings in column B. No idea how to
compute this.

Column D
D2:D51 is each state's population.
D54 is the total US population 281,421,906 (as of April 2000 census).

Column E
A formula or function to show each state's population D2:D51 as a
percentage of the whole 281,421,906 (D54).

What I want to do is copy this chart into Excel and then create new
columns to verify the ranks of the states (most populated to least
populated) 1-50. I also want to know which percentage each state's
population is of the 281,421,906 US total population as of April 2000
census.

If I just enter 1, 2, 3, 4, 5...50 in the rank column, then it might
still be able to create a bar chart and pie chart based on this data,
but I want to plug in the new population figures when I get them and
have Excel automatically recompute the rankings and percentages. That's
why I added another column for rank and one for percentage.

Here are the top 3 states, according to the chart.

D2 is 33,871,648 (California) Ranks 1
D3 is 20,851,820 (Texas) Ranks 2
D4 is 18,976,457 (New York) Ranks 3

I'm copying the chart according to rank, but I'd like to sort them
alphabetically by state without it affecting the data. Sorry if I'm not
explaining this well. I don't use Excel too often for this kind of stuff
and don't have much experience with it. It is Excel 2002 if that helps.

population percentages and ranks

I forgot that there are 51 figures (50 states + D.C.), so the total US
population will be in D55, not D54. Sorry bout that.

Merci sent a subspace message on 6/28/2006 8:22 PM:
I need help writing formulas or functions. The chart on this website is
out of date. There is newer census data, but I have to go to the library
to get it.
http://www.enchantedlearning.com/usa...pulation.shtml

Column A
State name

Column B
Ranking in numbers (copied from the chart on that web page)

Column C
A formula or function to verify the rankings in column B. No idea how to
compute this.

Column D
D2:D51 is each state's population.
D54 is the total US population 281,421,906 (as of April 2000 census).

Column E
A formula or function to show each state's population D2:D51 as a
percentage of the whole 281,421,906 (D54).

What I want to do is copy this chart into Excel and then create new
columns to verify the ranks of the states (most populated to least
populated) 1-50. I also want to know which percentage each state's
population is of the 281,421,906 US total population as of April 2000
census.

If I just enter 1, 2, 3, 4, 5...50 in the rank column, then it might
still be able to create a bar chart and pie chart based on this data,
but I want to plug in the new population figures when I get them and
have Excel automatically recompute the rankings and percentages. That's
why I added another column for rank and one for percentage.

Here are the top 3 states, according to the chart.

D2 is 33,871,648 (California) Ranks 1
D3 is 20,851,820 (Texas) Ranks 2
D4 is 18,976,457 (New York) Ranks 3

I'm copying the chart according to rank, but I'd like to sort them
alphabetically by state without it affecting the data. Sorry if I'm not
explaining this well. I don't use Excel too often for this kind of stuff
and don't have much experience with it. It is Excel 2002 if that helps.

population percentages and ranks

In E put

=D2/$D$55

format as percentage and copy down 51 rows will give you the percentage

For the ranking see help for RANK

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

"Merci" wrote in message
...
I forgot that there are 51 figures (50 states + D.C.), so the total US
population will be in D55, not D54. Sorry bout that.

Merci sent a subspace message on 6/28/2006 8:22 PM:
I need help writing formulas or functions. The chart on this website is
out of date. There is newer census data, but I have to go to the library
to get it.
http://www.enchantedlearning.com/usa...pulation.shtml

Column A
State name

Column B
Ranking in numbers (copied from the chart on that web page)

Column C
A formula or function to verify the rankings in column B. No idea how to
compute this.

Column D
D2:D51 is each state's population.
D54 is the total US population 281,421,906 (as of April 2000 census).

Column E
A formula or function to show each state's population D2:D51 as a
percentage of the whole 281,421,906 (D54).

What I want to do is copy this chart into Excel and then create new
columns to verify the ranks of the states (most populated to least
populated) 1-50. I also want to know which percentage each state's
population is of the 281,421,906 US total population as of April 2000
census.

If I just enter 1, 2, 3, 4, 5...50 in the rank column, then it might
still be able to create a bar chart and pie chart based on this data, but
I want to plug in the new population figures when I get them and have
Excel automatically recompute the rankings and percentages. That's why I
added another column for rank and one for percentage.

Here are the top 3 states, according to the chart.

D2 is 33,871,648 (California) Ranks 1
D3 is 20,851,820 (Texas) Ranks 2
D4 is 18,976,457 (New York) Ranks 3

I'm copying the chart according to rank, but I'd like to sort them
alphabetically by state without it affecting the data. Sorry if I'm not
explaining this well. I don't use Excel too often for this kind of stuff
and don't have much experience with it. It is Excel 2002 if that helps.

population percentages and ranks

You may also need to include separate population data for Puerto Rico,
the US Virgin Islands, and maybe Guam, American Samoa, etc.

Good luck!

population percentages and ranks

Thanks, I will write back if I can't figure it out.

Peo Sjoblom sent a subspace message on 6/28/2006 8:39 PM:
In E put

=D2/$D$55

format as percentage and copy down 51 rows will give you the percentage

For the ranking see help for RANK