Announcements

You can get these grade sheets from me during my office hours. Please download the Grades(zipped .xlsx file) from the course web and ensure that I have computed and entered your grade correctly. This file uses the last 5 digits of your student ID. I'll be entering hundreds of grades for students in my course this quarter, so even if I'm 99% accurate, I'm likely to record some incorrect grades. Note that all grades are recorded as integral values: I always round up (e.g., an programming score of 36.5 is recorded as 37).

If you do not pick up your returned work in class, you should pick it up during my office hours ASAP; I don't like keeping student grade sheets: it makes my office messier, and you don't get the benefit of the feedback I wrote.

Generally students did very well on this exam. Many of the problems were similar to the code you had on the sheets you brought to the exam, but you still had to understand and make changes to this code.

• Problem #1 (BothMax): Average for this problem was 96%. Some students used two IFs but only one IF/ELSE is needed. Some students declared a local variable, and changed the variable, not the spreadsheet.

• Problem #2 (SumToN): Average for this problem was 97% (the highest on the exam). Very few students did not increment a variable

• Problem #3 (Replicate): Average for this problem was 89%. Some students did not write a loop, or did not used the loop index correctly to store values in the spreadheet cells.

• Problem #4 (SumBeneath): Average for this problem was 83%. Some students did not just sum beneath the selected range, using a WHILE to do all the cells. Other students did not sum the values correctly; some tried using WorksheetFunction.SUM which was not allowed.

• Problem #5 (Replace): Average for this problem was 90%. Some students has no loop, some had an infinite loop, some did not use the incrementing index correctly to replace the required cells.

• Problem #6 (Powers): Average for this problem was 66%. Some students solved no part, some just the first part. Many students did not solve how to put 1 in the uppder-left hand corner of the selecton and then muliply by the power: a few put the power in the upper-left corner and always doubled it.

After I return your graded work in class on Monday, please download the Grades(zipped .xlsx file) from the course web and ensure that I have computed and entered your grade correctly. This file uses the last 5 digits of your student ID. I'll be entering hundreds of grades for students in my course this quarter, so even if I'm 99% accurate, I'm likely to record some incorrect grades. Note that all grades are recorded as integral values: I always round up (e.g., an programming score of 36.5 is recorded as 37).

If you do not pick up your returned work in class, you should pick it up during my office hours ASAP; I don't like keeping student grade sheets: it makes my office messier, and you don't get the benefit of the feedback I wrote.

• Problem #1 (DB1: Autofilter): Average for this problem was 100%.

• Problem #2 (DB2: Sorting): Average for this problem was 95%. One student didn't answer it; another switched the order of two fields to sort.

• Problem #3 (DB3: BOOLEAN on Books): Average for this problem was 89%. Some students did fill in the required count; others dis not quite filter correctly (chose a row too high or low).

• Problem #4 (DB4: BOOLEAN on Grades): Average for this problem was 86%. Some students computed the average incorrectly; a few students changed all the grades to Bs, instead of selecting non-A grades. Note that <> is the "not equal to" operator in Excel.

• Problem #5 (DB5: Advanced Filter on Books): Average for this problem was 82%. Some students did not duplicate the Year field, or used Min Year and Max Year (which are not fields); some students did not DCOUNT the number of correct records.

• Problem #6 (DB6: Advacned Filter/Data Table on Books): Average for this problem was 60%. Some students did not use a Data Table (I gave 1 point for computing all the correct answers with DSUM); some students could not get the DSUM to work at all.

• Problem #7 (DB7: Pivot Table on Grades): Average for this problem was 81%. Some students constructed overly complicated Pivot Tables (ans some numbers contained too many digits) that did not easily illustrate the information required (or did not answer the questions, or were vague).

• Problem #8 (DB8: Pivot Table on Hosptital): Average for this problem was 83%. Some students constructed Pivot Tables that were incorrect, or didn't produce the required chart with all the information in the required order or format. If I had a bit more time constructing the exam, I would have not chosen to repeat a problem from the lab.

• Problem #9 (DB9): Average for this problem was 69%. Some students didn't attempt this problem; others were not able to get the required data (how many books were bought each year); still others were not able to sort this information and extract just the top 15 publishers; a few did not construct the pie chart; finally, some did not answer the last two questions.

After I return your graded work in class on Monday, please download the Grades(zipped .xlsx file) from the course web and ensure that I have computed and entered your grade correctly. This file uses the last 5 digits of your student ID. I'll be entering hundreds of grades for students in my course this quarter, so even if I'm 99% accurate, I'm likely to record some incorrect grades. Note that all grades are recorded as integral values: I always round up (e.g., an programming score of 36.5 is recorded as 37).

If you do not pick up your returned work in class, you should pick it up during my office hours ASAP; I don't like keeping student grade sheets: it makes my office messier, and you don't get the benefit of the feedback I wrote.

There were lots of small mistakes on both assignments, which each had lots of parts.

After I return your graded work in class on Monday, please download the Grades(zipped .xlsx file) from the course web and ensure that I have computed and entered your grade correctly. This file uses the last 5 digits of your student ID. I'll be entering hundreds of grades for students in my course this quarter, so even if I'm 99% accurate, I'm likely to record some incorrect grades. Note that all grades are recorded as integral values: I always round up (e.g., an programming score of 36.5 is recorded as 37).

If you do not pick up your returned work in class, you should pick it up during my office hours ASAP; I don't like keeping student grade sheets: it makes my office messier, and you don't get the benefit of the feedback I wrote.

• On the Cannon Shell 2 worksheet, a few students did not define names or use all the names where they should be used (in formulas). The main purpose of this worksheet was defining and using names.

• On the Life worksheet, some students had incorrect formulas in the middle or bottom rectangles: of those who got it wrong, their formulas were often too complicated (and that was the source of their mistakes). Other students did not record the macros correctly, and some did not create buttons the needed buttor or did not attach macros to them.

After I return your graded work in class on Monday, please download the Grades(zipped .xlsx file) from the course web and ensure that I have computed and entered your grade correctly. This file uses the last 5 digits of your student ID. I'll be entering hundreds of grades for students in my course this quarter, so even if I'm 99% accurate, I'm likely to record some incorrect grades. Note that all grades are recorded as integral values: I always round up (e.g., an programming score of 36.5 is recorded as 37).

If you do not pick up your returned work in class, you should pick it up during my office hours ASAP; I don't like keeping student grade sheets: it makes my office messier, and you don't get the benefit of the feedback I wrote.

• Problem #1 (Name): Average for this problem was 93% (one student omitted this worksheet; another did not put the tab first).

• Problem #2 (Cryptography): Average for this problem was 68%. Some students were not able to complete the 4th code (mostly failing to get the letter: most generated the random number and computed their ranks). Most students were able to compute the Code equivalents for the Input Letter, fewer were able to calculate the correct code based on the Code To Use (it is a short call to the OFFSET function). Even fewer still were able to correctly produce the correct data table.

• Problem #3 (Taxes): Average for this problem was 72%. Some students did not define the names correctly (some scoped to the worksheet, others to the workbook) or did not use them in all the places they needed to be used. A few students were not able to lookup all the information and/or calculate the correct taxes; more students were not able to produce the correct data table. Threre were all sorts of small errors possible on in the graph: typically I took off 1/2 point for most errors here.

• Problem #4 (Macros+Buttons): Average for this problem was 84%. Most students wrote some kind of macro and created a button which I could push to run it. Many students had macros that selected a chart before converting it, so the user is not able to flip every charter back and forth (it says a few places to select the chart before recording the macro). A few students started/stopped recording a macro, but there was nothing in it.

After I return your graded work in class on Wednesday, please download the Grades(zipped .xlsx file) from the course web and ensure that I have computed and entered your grade correctly. This file uses the last 5 digits of your student ID. I'll be entering hundreds of grades for students in my course this quarter, so even if I'm 99% accurate, I'm likely to record some incorrect grades. Note that all grades are recorded as integral values: I always round up (e.g., a score of 26.5 is recorded as 27).

If you do not pick up your returned work in class, you should pick it up during my office hours ASAP; I don't like keeping student grade sheets: it makes my office messier, and you don't get the benefit of the feedback I wrote.

• On the Secretary+ worksheet in Lab #5, a few students put -5 in cell D2 instead of subtracting the value in cell B1. A few students did not fill in the average or the data table.

• On the Chart worksheet in Lab #5, many students put in formulas that worked without referring to the information in B2:B4; if this information changed, their formulas would be wrong. Some students did not correct put in conditional formatting to depend on the maximum score in a column.

• On the Randome+Sort worksheet in Lab #5, a few students referred to $B$21 in cells C2:C18, although that information was supposed to be ranked always with the highest value having the lowest rank. Some students did not fill in parts of this spreadsheet.

• On the Prices worksheet in Lab #5, a few students did not put in correct conditional formatting (showing orange); some others did not use the INDEX/MATCH or LOOKUP functions correctly.

After I return your graded work in class on Monday, please download the Grades(zipped .xlsx file) from the course web and ensure that I have computed and entered your grade correctly. This file uses the last 5 digits of your student ID. I'll be entering hundreds of grades for students in my course this quarter, so even if I'm 99% accurate, I'm likely to record some incorrect grades. Note that all grades are recorded as integral values: I always round up (e.g., a score of 26.5 is recorded as 27).

If you do not pick up your returned work in class, you should pick it up during my office hours ASAP; I don't like keeping student grade sheets: it makes my office messier, and you don't get the benefit of the feedback I wrote.

Problem #1 (Name): Average for this problem was 90% (some students omitted this worksheet, named the tab wrong, or didn't put the tab as the first).

Problem #2 (Formula): Average for this problem was 70%. Many student made small mistakes on some forumulas.

• Generally I deducted 1/2 point for a solution that was close to the correct one, and deducted 1 point a missing solution or one that was not close to correct.
• The I took off 1/4 point for small errors, like redundant parentheses.
• Common errors were misreating the formulas (e.g., writing (u*i*r)^2 when only r is squared), not putting denominators with multiplication in parentheses (needed in (b) and (c), which I identified as the most common error students make, not knowing how to use the AND function or <= relational operator in part (e), using RANDBETWEEN to generate the random numbers (that function returns only integer values), not using the & operator or IF funciton in (g).

Problem #3 (Profit-Loss): Average for this problem was 88%. Most students did very well on this problem.

• The most common error was not using conditional formatting for months with a loss, or not entering and copying a general formula for the Cumulative Profit/Loss row. Each was a 1-2 point deduction.

Problem #4 (Cheating): Average for this problem was 82%. Most students did well on this problem; many formatted small parts of the chart incorrectly. The most missed part was not using solid circular data points (many blue lines surrounding them) or getting the vertical gridlines to appear .

• Some students did not use the SUM function to add a large number of columns; some computing percentages between [0,1] instead of [0,100] as was specified; others did not format the cells to show no decimals.
• Charting problems were, not making axes and gridlines black, absent vertical gridlines, 100 as the maximum X and Y values, major fixed ticks at 20, minor ticks fixed at 5, not filling in the marker (solid red with red line) or legend correctly. Most errors were a 1/2 to 1 point deduction.
• Note that you needed to format the scales to be fixed, using the values specified in the problem.

Problem #5 (Scroll Bars): Average for this problem was 69%. Many students did well on this problem; some missed the protection part of the problem.

• You should know that raw scrollbar values are always positive, and you need a formula to compute the real values based on these. The general approach is similar to transforming random numbers from the range [0,1) to an arbitrary range. You figure the number of values (based on the range and step: range/step + 1), use that for the raw values (0 to whatever), then multiply that number by the step and, add the lowest value.

  For example, the last problem ([-5,5] step .1] needed 101 (10/.1 + 1) values at the .1 step, so the scrollbar goes from 0 to 100. We multiply it by .1 (the step) and add -5 (the lowest value).

• Some students missed small parts of the chart, most often not labeling the one data point with its X and Y values, not getting the right scrollbar to display (some students used the raw not real values).
• Some students did not do any protection (2 point deduction); some did the correct hiding of cells, but did not protect the worksheet (1 point deduction)

Problem #6 (Random Change): Average for this problem was 59%. Some students didn't get much beyond the random number generation; 4 students completely solved it (another 6 got 9.5 of 10 points). I basically graded it as follows

• 2 points for generating random numbers.
• 2 points for cumulative maximims. There were many ways to do this, including using an IF function or multiple ways to use the MAX function.
• 3 points for counting changes (which required some kind of IF function checking whether the maximum changed.
• 2 points for conditional formatting to make the right cells red.
• 1 point for computing the right answer (as simple as just using the =C1001 formula (some used the MAX function on column C).

  Problem #7 (Data Table): Average for this problem was 49% (by far the lowest on the test); 9 students completely solved it. Some students didn't get much beyond filling in the left side of the Data Table (worth .5 points if you did both). Putting the formula =E32 in cells H2 and/or H20 was worth 1/2 a point each. Putting in each Data Table was worth 1 point. A problem with Data Table is most likely to be on the next In-Lab Exam; please learn how to use this powerful feature if you have not already mastered it.

After I return your graded work in class on Monday, please download the Grades(zipped .xlsx file) from the course web and ensure that I have computed and entered your grade correctly. This file uses the last 5 digits of your student ID. I'll be entering hundreds of grades for students in my course this quarter, so even if I'm 99% accurate, I'm likely to record some incorrect grades. Note that all grades are recorded as integral values: I always round up (e.g., a score of 26.5 is recorded as 27).

If you do not pick up your returned work in class, you should pick it up during my office hours ASAP; I don't like keeping student grade sheets: it makes my office messier, and you don't get the benefit of the feedback I wrote.

On the Rabbits-Foxes worksheet, there were many kinds of errors.

• Some students did not protect the worksheet; when I protected the work sheet for them, sometimes input cells were locked. You need to understand how locking and protection work together.
• Some students did not use conditional formating to turn the raw scroller cells red when the user entered a value outside the range of the scroller.
• Some students did not ensure that the real scroller cells were always in the specified range (using either IF functions or min/max functions.
• Some students did not connect the scroll bars to raw cells, or the real cells (that depend on the raw cells) to the model.
• Some students did not put the points generated by the Period Tracer into the required charts, or did not have their markers and Y data labels present.
• Some students put a maximum value on the Y axis.
• Some students did not generally solve the Stable Populations cells (putting in just the values shown: these results depend in B7, B10, I7, and I10 )
• Some students made minor formatting errors.

On the Cannon Shell worksheet, there were fewer kinds of errors.

• Some students did not enter the formulas correctly, either for computing the real X and Y values, for using the IF function to stop these values from growing too big.
• Some students did have a marker for the Target series (just one point, so there are no lines between points), or had markers for the trajectory (here there should just be lines between points).
• Some students did not have 0 as the minimum Y value.
• Some students did not have the label for the Trajectory series (in the legend) change when the angle or speed changed.
• Some students forgot to solve to the minimum velocity (and its matching angle); or got it wrong (often by finding when the top of the parabola grazed the target.
• Some students made minor formatting errors.

On the Scheduler worksheet, there were few errors, although some students didn't attempt this problem.

• Some students used and OR function, but all three rules most naturally used a COUNTIF function.
• Some students used =3 for the third rule, but it should be >=3 to cover all the case 3 or above.

After I return your graded work in class on Monday, please download the Grades(zipped .xlsx file) from the course web and ensure that I have computed and entered your grade correctly. This file uses the last 5 digits of your student ID. I'll be entering hundreds of grades for students in my course this quarter, so even if I'm 99% accurate, I'm likely to record some incorrect grades. Note that all grades are recorded as integral values: I always round up (e.g., a score of 26.5 is recorded as 27).

If you do not pick up your returned work in class, you should pick it up during my office hours ASAP; I don't like keeping student grade sheets: it makes my office messier, and you don't get the benefit of the feedback I wrote.

Generally, the submissions showed good work by most students. Some students needed to read the assignment a bit more carefully: some put numbers in cells that were supposed to contain formulas and some didn't put in the general formulas discussed in the assignment.

In Assignment #2, I took off points for incorrect formulas (every incorrect formula computed an incorrect value for the data that I supplied on the worksheet, so students should have seen the problem). I took off points for using extra parentheses in formulas; few students put spaces in their formulas to make them easier to read. See my solutions.

On the Animals worksheet, most students got everything correct.

On the Interest worksheet, the most common problems were putting a number into cell B5 (it should copy the number from cell B2, so changing B2 will change the calculation) and putting a number for the interest rate into the cells in column C, instead of refering to cell $B$1 (so changing B1 will change the calculation). Please format the cells as I do (here you needed to right justify the text and also specify the number format to use commas and have 2 digits after the decimal point).

In Assignment #3, on the PI worksheet, some students didn't simplify the formula for random numbers (or didn't use the right formula), or didn't use just a single IF function in the Where column. Many students didn't refer to cells F2 and F3 in cells G2 and G3 and didn't refer to cells F7 and F8 in cells G7 and G8. In fact, the formulas in cells G2, G3, G7, and G8 are all the same, it is the conditions in the F column that are different. One student copy and pasted values (not formulas) into this worksheet (resulting in 0 points for this worksheet).

On the Interest+Chart worksheet, some students did not correctly duplicate all aspects of my chart (beside the problems mentioned above). Note that the label in the legend for the first data series should change as the interest rate changes, and moving the scrollbars also changes information for the last two data series in the chart.

On the Extended Growth worksheet, some students put a number into cell B12 (it should copy the number from cell C9, so changing C9 will change the calculation). The other common mistake was that the label in the legend for the data series should change as either the rate or capacity changes (I used cell A5 to capture this information). Some student made the Y axis fixed, but moving the scrolbar could make the line disappear off chart.

It is not enough to get the right values in the cells according to the Values-Solution workbook. Some of these cells depend on others (changing the others on the Values-Solution will change these cells too), so you must capture these relationships to get full credit. If you have a question about such possible relationships, ask about it on the course mailing list. It will also help if you read the instructions carefully (which sometimes highlight these relationships) and not just look at the Values-Solution. Of course, if you experiment with the values solution, and see how changes in one cell affects another, your worksheet should mimic that behavior.

After I return your graded work in class on Monday, please download the Grades(zipped .xlsx file) from the course web and ensure that I have computed and entered your grade correctly. This file uses the last 5 digits of your student ID. I'll be entering hundreds of grades for students in my course this quarter, so even if I'm 99% accurate, I'm likely to record some incorrect grades. Note that all grades are recorded as integral values: I always round up (e.g., an score of 26.5 is recorded as 27).

If you do not pick up your returned work in class, you should pick it up during my office hours ASAP; I don't like keeping student grade sheets: it makes my office messier, and you don't get the benefit of the feedback I wrote.

Generally, the submissions showed very good work by most students. This was a first assignment, and fairly cookbook/easy. Most points were deducted for not reading carefully and following directions (of course, please contact me if the directions are confusing). I took off smaller amounts for incorrect formatting (not putting material in boldface, not using commas in numbers where appropriate, not having the correct number of digits). I took off larger amounts for computing values incorrectly (wrong formulas in cells), or for omitting formulas in cells.

Finally, remember that only one student should drop off your pair's work.

Some students submitted no work. I contacted them by email; some joined the class late and will be submitting their work soon (email me when you do so, so I know to grade it). Generally I do not accept late work once the class gets up and running; turn in what you have finished whenever the assginment is due; if you did partial work, you will get partial credit.

I will never remove a message from this page, although a subsequent message may "cancel" a previous one; in such a case, I'll refer to the number of a canceled message in the message that cancels it.

Expect a few new messages to be posted here each week.

Read this page, along with the the course email discussions, daily.