Microsoft Excel 2010 Lesson 13: Practice Exercise 3
[Pages:7]Microsoft Excel 2010 Lesson 13: Practice Exercise 3
Start with the Nutrition Excel spreadsheet, which can be found on the course Moodle page. Look for the link to "Files for Excel Lessons."
These worksheets contain a lot of data. There is not time to enter each cell individually, so practice time-saving techniques: copy/paste, filling cells, automatic formatting, etc. It will be necessary to use these shortcuts on an exam to finish in time.
The data in the "Nutrition" spreadsheet has been extracted from a web site of government data, .
1. Nutrition contains three worksheets. Change the tabs to read "Costs," "Benefits," and "Persons," respectively.
2. Make the Costs worksheet look like the example, part of which is shown on the next page.
Set the heading cells up to Wrap Text. To use the Enter key within a cell so you space down to
the next line within a cell, hold the Alt key down when you press Enter.
Totals costs = Total Benefits plus All Other Costs Percent benefits = Total Benefits/Total Costs Cells in column G automatically turn red if they contain less than 93% Cells in column H display "Too Low" if corresponding cell in column G is less than
93%; otherwise display "OK." Rows 1 through 6 are stationary. They don't move if you scroll down the worksheet. Column A is stationary. It does not move if you scroll right across the worksheet. The headings of the table are shaded gray. Thick border around table and group of headings In the block J5 ? L6
o The cells are yellow o Enter the year you desire in the top right o The corresponding Total Cost and Percent Benefit are automatically displayed The date is that of the current day and is displayed automatically
Excel: Practice Exercise 3
1
18 October 2012
Excel: Practice Exercise 3
2
18 October 2012
3. Make the Benefits worksheet look like the example, part of which is shown on the next page.
Note that the months and years have been separated in two separate columns Average monthly benefit per person = Benefit Costs divided by number of Persons Average monthly benefit per household = Benefit Costs divided by number of
Households Notice the subtotals on the worksheet. When the subtotals are in place, cell E49 should contain a grand total. Each percent in column H is the Benefit Costs divided by the Grand Total.
o For example, cell H6 should contain 1.80% which is equal to $3,697,627,592 divided by the grand total, which happens to be $ 205,537,723,180.
You should be able to collapse the worksheet and only show the subtotals for each year.
Excel: Practice Exercise 3
3
18 October 2012
Excel: Practice Exercise 3
4
18 October 2012
4. Modify the Persons worksheet so is easier to read.
Answer the questions below. You should be able to use Excel tools we have learned to answer each question quickly and easily.
Now many states and territories had average monthly participation greater than 750,000 in FY 2008?
How many states and territories had average monthly participation between 500,000 and 1,000,000 in FY 2010?
In FY 2011, what state or territory had the smallest participation? What state or territory had the highest participation?
What is the median of the average monthly participation in FY 2011? What state corresponds to this FY 2011 median?
What are the top ten values of average monthly participation for all states from FY 2007 through FY 2011? (Hint: Use conditional formatting on the entire table) What states had these top ten values?
--------------------------------------------------------------------------------------------------------------Answers to the questions
12 [used Filters]
15 [used Filters]
Smallest = 22,655 (Virgin Islands); Highest = 3,977,273 (Texas) [Used Filters and
sorted]
614,704 (Oklahoma) [Used median function to find median; Used Filters and sorted
to make state easy to find]
States are California (3 values); Florida (2 values); New York (2 values); Texas (3
values)
[Used conditional formatting, Top/Bottom rules; Top 10 items]
---------------------------------------------------------------------------------------------------------------
More to do on the following pages.
Excel: Practice Exercise 3
5
18 October 2012
5. Create a new worksheet and name it Statistics. This worksheet will draw its data from the other worksheets. A picture of a portion of this worksheet is shown on the next page.
Average Monthly Participation by FY comes from the "Benefits" worksheet, using data for persons rather than households.
Average Monthly Participation by State comes from the "Persons" worksheet
Average Participation per Year comes from the "Costs" worksheet
Add a comment to the heading of each table explaining what worksheet the data was drawn from. This is illustrated for the top table in the picture on the next page.
The title cell of each table is colored yellow.
The heading cells are colored gray
To compute the mean values, use the Average function
Sort the "Average Participation per Year" table from highest participation to lowest.
When you have the three tables constructed, fix the spreadsheet so that rows 12 and above are stationary when you scroll down.
Excel: Practice Exercise 3
6
18 October 2012
Excel: Practice Exercise 3
7
18 October 2012
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- go with microsoft excel 2010 comprehensive
- advanced excel vlookup h pivot tables e 2010
- excel 2010 advanced
- introduction to ms excel 2010
- introduction to microsoft excel 2010 alison
- microsoft office professional 2010 step by step ebook
- microsoft excel 2010 basics pagosa springs
- excel basics microsoft office 2010
- microsoft office excel 2010 for dummies
- excel 2010 quick reference
Related searches
- microsoft excel 2010 user guide
- microsoft excel 2010 instruction manual
- microsoft excel 2010 manual pdf
- microsoft excel 2010 guide pdf
- microsoft excel 2010 training pdf
- microsoft excel 2010 user manual
- microsoft excel 2010 free download
- microsoft excel 2010 free download windows 10
- microsoft excel 2010 help guide
- microsoft excel 2010 free templates
- install microsoft excel 2010 free
- microsoft excel 2010 info