Calculating In Excel

[Eboneyedk]

Excel Spreadsheet Assignment

--------------------------------------------------------------------------------

I have an assignment that I'm not quite understanding how to do, and
would like some clarification. I would appreciate anyone's help please.
Here goes:

This is an Excel Spreadsheet Assignment.

The directions:

1. "Modify a spreadsheet to "include percentage use calculations of
Internet use for shopping over the Internet for age, education level,
and income category." (Only the Education Level Section's %'s are
given).

2. "Create appropriate graphs to illustrate how the distribution of use
of the Internet for shopping varies across age, income, and education
level categories."


For example: Section1

Education Level/ Yes Have Used / No Have Not Used /% Using Internet to
Shop/Pay Bills
-----------------------------------------------------------------------------------------
Less than HS / 598824 / 32354711 / 1.8%
HS Grad / 5104952 / 60930000 / 7.7%
Some College/No Degree/ 6613400 / 32290000 / 17.0%
Assoc. Degree / 7840540 / 38855612 / 16.8%
Bach. Degree / 9581979 / 22830000 / 29.6%
Masters Degree or Higher/ 5196040 / 10280934 / 33.6%

For the above section, the %'s are given, but for the next two sections
(2nd section: Age), (3rd section: Household Income), the %'s are not
given.


For example: Section2

Age / Yes / No / (this area is blank)
-------------------------------------------------------------------------------------
10-14 / 557160 / 19646353 / blank
15-19 / 1607513 / 18251159 / blank
20-24 / 2670194 / 15597505 / blank

and it continues all the way to age 75 and older.

Section 3:

Household Income / Yes / No / (this area is blank)
------------------------------------------------------------------------------------
Less than $5000 / 285666 / 4960488 / blank
$5000 to $7499 / 152549 / 4727749 / blank
$7500 to 9999 / 95264 / 4833534 / blank

and it continues all the way to $75000 or more.

So I would like to know how:

1. "Modify a spreadsheet to "include percentage use calculations for
age, education level, and income category." (Only the Education Level
Section's %'s are given).

2. "Create appropriate graphs to illustrate how the distribution of use
of the Internet for shopping varies across age, income, and education
level categories."

 

[JLatham]

Getting the percentages is fairly simple, the hardest part is formatting the
answer to show as 1.85% instead of 0.0185...

The basic formula to use in column D for each row is
=Bn/Cn
where 'n' is the row number you are on. That is going to give you a number
that appears as a regular decimal, like 0.018508 for that Less than HS Grad
sample, and should give 0.028359 for the 10-14 age group check.

You can fill that formula on down the sheet as needed to calculate the other
values. Search Excel Help for 'fill data in worksheet cells' and once you
find that look for the sub-topic 'Fill formulas into adjacent cells' and
follow those instructions.

But that leaves you with a bunch of decimal numbers and you want them to
look like percentages, so click the column indicator for the column they are
in (probably column D) and then click the % icon. They may round to the
nearest whole percent, like the 0.018508 may show up as 2%. So use the
[Increase Decimal] icon to show more precision - it is the button two buttons
to the right of the % icon button.

To create a chart based on a group's contents, with things laid out the way
they are now, try using the given information (education level) as a first
effort.

Choose Insert | Chart from the menu and in the first window choose the type
of chart you want. One of the column or bar types should work nicely. On
the next page of the Wizard indicate the area is from the "Less than HS" cell
over and down to the percentage for Masters Degree or Higher percentage entry
in column D.

This gives you more information than you want, and the percentages are so
small they are hidden. Click on the Series tab and delete Series 1 and
Series 2, leaving only Series 3 which are the percentages. Continue on with
the wizard making what ever choices you feel are needed and you should have a
nice chart. Repeat for the other data groups.