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.
*Sorting records
*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 decimal places.
*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)

Video Tutorials:

copying & parsing data from ASCII (CSV) to EXCEL csv2xls.avi
Concatenation of strings in excel by formula concat.avi
Insert rows & columns, extend a series by copy insertno.avi
Tools addins to add in data analysis tools (BA250 site) addins.avi
Histogram Procedure (BA250 site) histgram.avi

Merge Videos for Office XP
Sorting in EXCEL and print to 1 page exclsort.avi
1) prepare spreadsheet data for use:
 		 mergprep.avi
2) Merge into mailing labels in WORD wordmerg.avi

Required- Two full-page printouts of the datasheet plus a page of labels  (three sheets total):
    1. 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.
    2. Replace Dennis Adam's name with yours, fill in projected data for yourself, and sort by name, ascending.
    3. Be sure to print all to one page.

      4.  
    4. The merge exercise

    Critical features include:
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 rbanis@jinx.umsl.edu

return to BA103 syllabus
 

goto EXCEL exercise 1, jobrate