I'm sending this direct because I am pasting a table set below as an
explanation, and I don't think it will display properly in the list's text
based email. This way you'll get a HTML version to view. :)
Not being a programmer or a mathematician, I don't know if Excel's handling
or Calc's is more correct, however, here is a workaround:
=SUM(L2:O2)-MINA(L2:O2)
The MINA function is exactly the same function as MIN, except it allows
Alpha characters and treats all Alpha characters as a zero. Thus, you can
enter text into the cell of the test they missed. For example:
Test 1 Test 2 Test 3 Test 4 Top 3 Total
90 100 MISSED 98 288
MISSED 85 87 88 260
98 99 94 92 291
50 65 66 MISSED 181
95 MISSED 85 92 272
100 95 92 88 287
0 5 22 30 57
65 66 72 51 203
I also went an extra step and added conditional formatting to the "Test *"
columns:
If Cell Value = "Missed" use style Misses (My own style, colors text
red.)
You could also use this to color text according to pass/fail for quick
visual reference. Define a color coding/formatting system, create styles
based on that, and use conditional formatting to apply the appropriate color
style/format to the cell based on the test score. Apply a separate
conditional formatting set to the total column since the numbering is
different, and you have a fast visual reference of who passed and who
failed, or whatever type of system you use.
I know, you didn't ask about conditional formatting, but I tend to spout
either useless info or more than you asked for. :)
Hope this helps!
Sincerely,
Andrew Robertson
Export Manager
derma e(r) Natural Bodycare
ph. 805-582-2710 xt. 244
fx. 805-582-2730
[EMAIL PROTECTED]
www.dermae.net
-----Original Message-----
From: Brian Hassard [mailto:[EMAIL PROTECTED]
Sent: Friday, May 19, 2006 2:04 PM
To: [email protected]
Subject: [discuss] excel->oocalc incompatibility leads to grading "gotchas"
Ladies and gentlemen,
In a course I just taught, the lowest of four test grades
was to be dropped. This is done in my grading worksheet
by forming the expression
=SUM(L2:O2)-MIN(L2:O2)
where the test data is in L2, M2, N2, and 02.
Most students took all four tests, but some took only three tests.
I created the spreadsheet with Excel, and if a student had not
taken a particular test I left the corresponding cell blank.
Then I transferred the grading ".xls" spreadsheet to a Linux
system (I much prefer Linux) and started working on the
spreadsheet with oocalc (OpenOffice.org 2.0).
Now for the GOTCHA: The grades for the students who took only three
tests, suddenly decreased! If I hadn't noticed what happened,
I could easily have given students incorrect grades.
The explanation is as follows:
MIN under Excel considers blank cells to contain zero data, but
MIN under oocalc ignores blank cells.
Suppose the test grades were 80, 60, (didn't take), and 70.
With Excel, the MIN of the four test grades is 0
and the sum of the three best test grades is 210.
With oocalc, the MIN of the four test grades ignores the blank
cell and finds the minimum of 80, 60 and 70, that is, the 60.
The sum of the three best test grades is wrongly computed as
(80+60+70)-60 = 150.
Folks will respond: "Why not just put zeros in the spreadsheet for the
missing data?"
Well, it's a question of taste. I prefer to use blanks for cases in
which a student missed a test, to reserve the number 0 for the case in a
student took a test and got 0 points.
IMHO, in this case Excel does it right and oocalc does it wrong.
I have made several attempts to get people to convert from Windows to
Linux, but issues like this one in which just moving a grading
spreadsheet might cause students to end up with incorrect grades, are
real deal breakers.
Brian Hassard,
Department of Mathematics,
University at Buffalo, SUNY
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
