Mathematicians we is, operations-optimization experts we ain't

May 11, 2007 8:45 AM

Please help me set up a spreadsheet to tally certain courses taught by our faculty and keep track of their "values". This must be a solved problem in the business-savvy world, but, alas, we are ivory-tower types and utterly bamfoozled.

Our department has a point-based system for who must teach the so-called "large lecture" courses. It works like this: there are 11 courses (identified by number, e.g. 183, or code, e.g. 397c), each of which is assigned a point value from 1 to 4. There are potentially several sections of each course. Each faculty member gets the sum of the points for the courses they've taught in the last 10 semesters. Low scorers are at the top of the list for next year's assignments.

Our current system is charmingly 20th-century. I have a spreadsheet with a faculty-name for each row, and a semester for each column. The entries are just lists of which courses that person taught in that semester. (In particular, there may be more than one course in a box.) Then some drone (yrs truly) goes through with a pencil, assigning the appropriate points to each course and adding them up by hand.

The only better thing I can think of (and you'll see that this is a pure mathematician's approach to the problem) would be to have a 3-dimensional spreadsheet, with axes labeled by faculty-names, semesters, and course-names, fill it with 0's, 1's, 2's, etc., then do some kind of weighted-sum magic on it. Surely there's a better way.

posted by gleuschk to Computers & Internet (10 answers total)

Surely you have some of this information in some other file. What you really want is a macro that will fetch as much of the required information as possible from other sources, and then spit out an answer.

I don't think there is any truly simpler way than a 3d spreadsheet. You can just roll the 3rd dimension into additional columns or rows, or you can put more than one piece of information in some cells, but inherently you have instructor, course, and semester, and that is three dimensions.
posted by Chuckles at 9:15 AM on May 11, 2007

You can do a 3d spreadsheet in Excel. Use different sheets, say, one sheet per semester. Range references in Excel can emcompass a range of pages as well as 2d row/column data. So if that's a model you like, it's easily do-able.
posted by Wolfdog at 9:21 AM on May 11, 2007

I think you can just do this in Excel, and if you're already familiar with Excel that would be easiest.

Each course number (eg Calculus 101) has a corresponding value (like 4), yes? So why not set up Excel so that whenever you type in Calculus 101 to a given box, the program will fill in the value into the neighboring box. So for each semester, you would have two neighboring columns, and all the entering of values would be done automatically. (After you do the initial work of setting up the instructions for Excel that give it the values for each course.)

Then set up Excel so it will sum the values column.

(I read your initial question as a David Horowitz anti-academic-freedom "values"-based witchhunt. Yikes.)
posted by LobsterMitten at 9:24 AM on May 11, 2007

My solution imagines that you have a row with faculty name, and to the right of that a box with the name of the first course that person taught that semester, and below that a separate box with the next course he taught, and so on. That is, one cell per course. So each faculty member's name will have several cells in a column next to it; faculty names will be vertically separated by several cells.
posted by LobsterMitten at 9:28 AM on May 11, 2007

And you'd have the values of the courses summed by the computer. Then you can tell it to sum across the totals for Semester1, Semester2, and so on, to get the faculty member's final score.
posted by LobsterMitten at 9:31 AM on May 11, 2007

Can you sneak over to the MIS department, hit someone over the head, and ask them to do an Access database? Maybe I've had too many years of Comp Sci classes, but your problem fits my mental model of a relational database.
posted by zabuni at 9:33 AM on May 11, 2007

Oh, and for going the route of an Excel spreadsheet, the use of lookup tables might help with matching course to point values.
posted by zabuni at 9:38 AM on May 11, 2007

Best answer: Check your e-mail for a simple Excel example. If anyone else wants to see it, my address is in my profile.

Yes, Access would ultimately be better for a relational database such as this, but given the (assumed) small number of records, Excel will work just fine.
posted by desjardins at 9:39 AM on May 11, 2007

Forgot to preview. I basically did what LobsterMitten and zabuni suggested.
posted by desjardins at 9:41 AM on May 11, 2007

Response by poster: Sweet! desjardins' example is almost exactly what I need -- I think if I combine it with LobsterMitten's multi-row suggestion, it'll be perfection. Thanks to everybody.
posted by gleuschk at 10:28 AM on May 11, 2007