EXCEL Exercise 2 BA103
total 80 pts, weight 8%
When you are finished this exercise, you will have demonstrated
the following skills:
*Assembly of a spreadsheet to calculate grades with weights and maximum
possible point scores.
*Importing data from a comma delimited ASCII file, cutting and pasting
to move data across sheets. Data (grddat.csv )can
be downloaded from the webpage.
*Use of statistical Functions.
*Analyzing a data distribution to prepare a bar graphshowing the score
frequency distribution. (histogram Procedure)
*Adjusting number formats in ranges to give the correct formats and
*You can project your own score in the course through what-if analysis
(You need not hand this in).
*merging data into word from a generic flatfile database (EXCEL used
as an example)
|copying & parsing data from ASCII (CSV) to EXCEL
|Concatenation of strings in excel by formula
|Insert rows & columns, extend a series by copy
|Tools addins to add in data analysis tools (BA250 site)
|Histogram Procedure (BA250 site)
Required- Two full-page printouts of the datasheet plus a page
of labels (three sheets total):
Hand in a printout of the sheet that looks like the one on the handout
(see graphic below, if you don't have the handout), except with the records
sorted from highest to lowest scores. I included example formulas with
arrows to help you. You need not show these. This sheet, with the original
data, will have the right calculated values if all the formulas are correct.
Replace Dennis Adam's name with yours, fill in projected data for yourself,
and sort by name, ascending.
Be sure to print all to one page.
The merge exercise
Critical features include:
the formulas work. this is judged by having the right values in the bottom
your name is on it
graph looks right
axes are labeled
text box on graph
some kind of lines and shading
printed all to one page for each of the two printouts
numbers show and are reasonably legible
Hints and tips:
*Import the data into an empty sheet and use insert to put the data
into the correct cells and leave room fro headings and formulas.
*fill in column 1 by entering the first few numbers, selecting the
range A6 to A45, and clicking on Edit/Fill /Series/column autofill
*The functions in cells C46 to C48 can be copied across to the other
columns. be sure to format row 48 and column W to show %.
*The formulas in columns L, S, V, W, X only need to be entered
once. They can thereafter be copied to the other rows with automatic adjustment
of the cell references (note which are absolute).
*Sorts are a phenomenal pothole in EXCEL. Be VERY CAREFUL. To sort
the records, use Data/Sort; specify the Range in the lowest box (be sure
to cover the whole data range from A6 to X45, do not include column headings
or the statistics rows at the bottom, as this would make a mess), click
the descending radio button to get the order from highest to lowest. It
might be a good idea to SAVE before sorting. If you goof, remember the
UNDO function under Edit.
*Type in the distribution bin values (range C50..C61) before doing
the analysis via Tools/Data analysis. Set the Range to X6..X45, and the
bin range to C50..C61.set output range to someplace convenient.
*The bar graph is used to display data that results. Be sure to set
the title and axis labels.
* Printing all to one page: when printing, go to file Print click
the preview/page setup button and set size to print all to 1 page by 1
page. if this option isn't there, that means you came into print
with some range selected. Go back to the Ready mode (out of the menu)
and deselect the range by clicking a cell someplace else in the sheet,
then come back in to print.
Discussion on some of the details
Sorting rows of data in EXCEL
This is a humongous pothole in EXCEL. You have to be very careful or
things will get all screwed up (technical term). Save a backup
under a different name before you try sorts as it is possible to completely
scramble the sheet if a range is selected wrong and EXCEL makes it very
difficult to tell what the sort range is. It is most critical to
include all relevant columns in the range when you sort. Otherwise, the
records will get scrambled because fields will no longer correspond.
Extra credit is available for:
*Designing a macro to print individual grade summaries for each student.
*Revising the design to allow analysis of more than one section through
use of 3-d sheets.
*Devising a formula to record the best 5 of 7 quiz grades
*Good suggestions on better ways to do parts or the whole sheet.
* Almost anything else useful and different.
VIEW A GRAPHIC OF THE SHEET You Might not
want to do this if you're working over a modem as the graphic is large
(400 Kilobytes) and could take several minutes to download.
last modified, February 19, 2002. Comments to webmaster firstname.lastname@example.org
return to BA103 syllabus
goto EXCEL exercise 1, jobrate