Excel functions to help check marks

When managing student marks, I usually have marks from various assessments in separate files. When merging I need to be sure students get their marks and no one else’s! After examining the class data, I email students who did not submit work by checking the module database for their emails. Just in case there are problems I don’t know about. And when marks are uploaded to the exam system along with exam results, it is important to ensure the right marks are integrated.

While MS Excel makes marks management much easier, it is also easy to make mistakes. The few simple functions in Excel that I list below helps me double and triple check as well as highlight problems. And the very important final stage is ground-truthing of student marks – checking a random selection of actual submissions against the spreadsheet; than I finally feel safe!

  • =exact(A1, B1) – very important for checking when you merge marks from separate data sheets. I check against matric numbers and names. The functions returns a TRUE or FALSE value. I “fill down” to make the comparison for the entire column. At the bottom I use a “countif false” and that indicates the number of mismatches. Then I use conditional formatting in the column to highlight the FALSE matches.
  • =fixed(A1, 2) for two decimal places – “exact” comparisons are not possible between numbers that are rounded up to display a certain number of decimal places. The displays may actually reflect numbers truncated from a sting of digits and so are not exactly the same as a number corrected to a fixed number of decimal places. Thus when comparing two columns of numbers, I derive fixed numbers first (in separate columns) before making an “exact” comparison.
  • =countblank(A1:A200) – counts the number of empty cells in a column. Helps in finding out who has not submitted work or if marks have not been allocated. Usually used in combination with conditional formatting.
  • =countif(G1:G200, “<5″) or (J1:J200,”false”) – counts the number of cells below a specified value. Critical in identifying the number of false matches from an exact value comparison (i.e. the two columns are not an exact match). Also identifies poor form – if consistent across a semester, it probably highlights a student for counseling. Usually used in combination with conditional formatting.
  • Format> Conditional Formatting… – specify a colour for cells that are empty or within a range of values. Helps to visualy highlight problems and track student performance. And when a row of coloured cells jump out at you from a single row, you might have a student who left the course or someone in trouble.

Know of any other useful functions I might find handy?

Give twhirl a whirl

I saw this in VanTan‘s furious Magnolia updates, and now three others are on it as well. For good reason too.

Multiple-platform, multiple accounts, groovy themes, free, and a whole bunch of features I had wished were in Twitterific, which mainly bugged me because of it’s frequent choking.

Go on, give twhirl a whirl. There, I said it!

Talk to SCSC on the impact of marine trash

“Biodiversity in Singapore and the threat of marine trash,” by N. Sivasothi a.k.a. Otterman,
Coordinator, International Coastal Cleanup Singapore

Singapore Command and Staff College, SAFTI MI
Friday, 16th May 2008: 5.30pm.

About the talk: Singapore is still host to patches of coastal and marine ecosystems which are home to otters, dugongs, sea stars, octopus, dolphins, hundreds of species of fish, sea snakes, turtles and even the odd croc or two! Creatures new to science are still being discovered today and naturalists are regularly delighted by encounters with a variety of creatures. A supportive public is increasingly engaged and hear about the visitors who are still excited about Chek Jawa today.

A barrage of pressures threaten all of this – development, poaching and environmental accidents, and this is on-going. This talk examines an uncommon element – marine trash. Plastics are so impactful in the world’s oceans today that United Nations Secretary-General Kofi Annan announced (2004), “Marine trash, mainly plastic, is killing more than a million seabirds and 100,000 mammals and sea turtles each year”.

For 17 years now, volunteers with the annual International Coastal Cleanup Singapore have removed up to 11 tonnes of trash annually in just 90 mins. The day’s categorisation of trash contributes to a global snapshot of the curseof our oceans. In Singapore, almost 90% of this is plastic with almost two-thirds originates from land. Abandoned nets amplify the impact by entangling and killing birds, snakes, crabs, horseshoe crabs and fish. In separate exercises, volunteers often remove 100 or more endangered horseshoe crabs from “ghost nets” in our mangroves.

We’ve had bright moments – after a decade of cleanups at the Buloh-Kranji mangroves, seedlings are now growing where plastic once choked the shores. More than 3,000 individuals from over 40 organisations have taken to year-round cleanups, and without invitation too! The global and local data has triggered further action by the media, groups, organisations and institutes.

About the speaker: N. Sivasothi, a.k.a. ‘Otterman’ is an instructor at National University of Singapore. The mangroves have been the backdrop to his research, education and conservation activities with the habitat, crabs and otters from the late 1980’s. He coordinated his first mangrove cleanup in 1997 and never stopped. The ICCS Otters, a team of volunteers, have emerged from the cleanups to assist in this continually growing programme.