Re: Tables: Calculating cumulative and final grades

[Kene Meniru <kemeniru@gmail.com>]

I thought as much. Thanks for the help.
I have installed the code for the number sequencing which is a bigger headache than the cumulative grade. I never thought that field code can be 
used along with column code. Interesting info.

By the way using a spreadsheet is not as efficient for me as using orgmode and orgmode is much more fun. Having discovered this list, there are 
so many treasures that I am finding in the archives. Glad to be here.

Juri Krivov wrote:
> 
> 
> On Fri, Aug 1, 2008 at 9:40 AM, Kene Meniru <kemeniru@gmail.com 
> <mailto:kemeniru@gmail.com>> wrote:
> 
>     Thanks to everyone who helped me with the letter grading and ranking
>     formula for columns 17/Q and 18/R above. I wish to trouble you all
>     again with two requests. After incorporating the two solutions above
>     and entering my summer class grades, it was so sweet that I would
>     like two other things automated.
> 
>      |   |    | Total grades for   |   100 |  100 |   89 |  84 | 100 |
>     100 | 204 | 400 | 100 | 100 |         |         |         |   |    |
>        |
>      | ^ |    | each evaluation    |    DG |   EG |   FG |  GG |  HG |
>      IG |  JG |  KG |  LG |  MG |  Single | Overall | Overall |   |    |
>        |
>      |   |    | Percentage weights |     6 |   10 |   10 |  10 |   7 |  
>     7 |  14 |  12 |   0 |  24 |     Now |     Now |   Final |   |    |    |
>      | ^ | SQ | Student Names      |   E01 |  E02 |  E03 | E04 | E05 |
>     E06 | E07 | E08 | E09 | E10 | E-Grade | C-Grade | F-Grade | L | RK |
>     SQ |
>      |---+----+--------------------+-------+------+------+-----+-----+-----+-----+-----+-----+-----+---------+---------+---------+---+----+----|
>      | # |  1 | lastA, first       | 95.23 | 87.7 |   76 |  75 | 100 |
>     100 | 204 |     |     |     |    0.00 |   93.67 |   59.95 | A |  1 |
>      1 |
>      | # |  2 | lastB, first       |   100 | 64.7 |   61 |  63 | 100 |
>      60 | 204 |     |     |     |    0.00 |   81.29 |   52.02 | B |  4 |
>      2 |
>      | # |  3 | lastC, first       | 85.69 | 60.2 |   66 |  68 | 100 |
>      90 | 130 |     |     |     |    0.00 |   76.40 |   48.89 | C |  7 |
>      3 |
>      | # |  4 | lastD, first       | 76.15 | 44.6 |   41 |  54 | 100 |
>     100 |  85 |     |     |     |    0.00 |   62.34 |   39.90 | D | 10 |
>      4 |
>      | # |  5 | lastE, first       | 80.92 | 43.6 |   68 |  63 | 100 |
>      70 | 173 |     |     |     |    0.00 |   75.20 |   48.13 | C |  8 |
>      5 |
>      | # |  6 | lastF, first       | 85.69 | 80.4 | 50.5 |  42 | 100 |
>     100 | 196 |     |     |     |    0.00 |   80.17 |   51.31 | B |  5 |
>      6 |
>      | # |  7 | lastG, first       | 95.23 | 56.6 |   55 |  65 | 100 |
>      60 | 157 |     |     |     |    0.00 |   73.85 |   47.27 | C |  9 |
>      7 |
>      | # |  8 | lastH, first       | 80.92 | 50.7 |   64 |  70 | 100 |
>      90 | 159 |     |     |     |    0.00 |   77.60 |   49.66 | C |  6 |
>      8 |
>      | # |  9 | lastI, first       | 95.23 | 73.2 |   88 |  73 | 100 |
>      70 | 183 |     |     |     |    0.00 |   87.61 |   56.07 | B |  2 |
>      9 |
>      | # | 10 | lastJ, first       | 85.69 | 47.2 |   28 |     | 100 |  
>     0 |     |     |     |     |    0.00 |   31.26 |   20.01 | F | 11 | 10 |
>      | # | 11 | lastK, first       |   100 |   69 |   56 |  77 | 100 |
>     100 | 164 |     |     |     |    0.00 |   83.77 |   53.61 | B |  3 |
>     11 |
>      |---+----+--------------------+-------+------+------+-----+-----+-----+-----+-----+-----+-----+---------+---------+---------+---+----+----|
>      |   |    |                    |       |      |      |     |     |  
>       |     |     |     |     |         |         |         |   |    |    |
>     #+TBLFM:
>     $14=($13/$MG)*100;%.2f::$15=(((($E01/$DG)*$4)+(($E02/$EG)*$5)+(($E03/$FG)*$6)+(($E04/$GG)*$7)+(($E05/$HG)*$8)+(($E06/$IG)*$9)+(($E07/$JG)*$10))/($E01+$E02+$E03+$E04+$E05+$E06+$E07))*100;%.2f::$16=((($E01/$DG)*$4)+(($E02/$EG)*$5)+(($E03/$FG)*$6)+(($E04/$GG)*$7)+(($E05/$HG)*$8)+(($E06/$IG)*$9)+(($E07/$JG)*$10)+(($E08/$KG)*$11)+(($E09/$LG)*$12)+(($E10/$MG)*$13);%.2f::$17=if($15<60,F,if($15<70,D,if($15<80,C,if($15<90,B,A))))::$18=find(rsort(@I$15..@II$15),$15)::$19=$2
> 
> 
>     First is the cumulative grade column 15/O.
>     Presently, I add the available grades divided by the available
>     total. When I grade each additional assignment, I add that to the
>     total in this column's formula manually which gives me the
>     cumulative. Is there a way to automatically do this or am I
>     expecting too much? I have removed grades in column K, L, M so I can
>     show the difference in calculation between column O and P. Assuming
>     there are 10 assessments for the class in the semester and 7 have
>     been graded. Column O shows the grade for each student as 100% of
>     the total grade. Column P shows the appropriate fraction of the
>     total grade for the assessments as a fraction of the overall knowing
>     that there are 3 more assignments ungraded. Column O gives me a
>     better snapshot of each students performance at any point in time.
> 
> 
> To do automatically it seems quite hard, even in real spreadheet 
> programs it require many if structures, checking if field is empty or 
> no.  Maybe you make extra copy row with "percentage weights", but make 
> zero weights of assignments not yet done, and use new weight row for 
> column 15?  Then you put new weight in new row to make assignment active 
> for "cumulative grade"? 
>  
> 
>     The second problem is numbering the students in column 2/B.
>     I want to manage the number for each student so that it does not
>     matter when I add or remove a student in the roster. Right now
>     students are registering for the fall classes and I add their names
>     according to their alphabetized last names. I have to re-sequence
>     this column constantly. I appreciate all the help that anyone can
>     throw at these problems.
> 
> 
> This I do by using field formula for first field, then column formula 
> for rest:
>  
>      $2=@-1+1::@5$2=1
> 
> 
> Juri
>  
> 

-- 

Kene Meniru
::::::::::::::::::::::::::::
keMeniru@gmail.com