population percentages and ranks

[Merci]

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/states/population.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
D251 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 D251 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.

 

[Merci]

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:

 

[Peo Sjoblom]

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

 

[noneofyourbiznez]

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

Good luck!

 

[Merci]

I would, but they do not appear in the source chart, so I don't have
data on them.

(e-mail address removed) sent a subspace message on 6/29/2006 1:06 AM:

 

[Merci]

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: