Wednesday, December 1, 2010

De Slimste Mens Ter Wereld

(Nederlandse versie: zie onder)

Jonathan Huyghe has made a nice little flash tool to let you play the popular Flemish TV show ‘De slimste mens ter wereld’ (The smartest person of the world) at home.
Because I found it quite complicated to prepare the game, I made a little Excel template that should make it more straightforward to make all preparations for it. It can easily generate the required ‘antwoorden.txt’ file with the correct syntax and it can print out cards with instructions to be used by the host during the game play.

Update 01/2016: An alternative version (independent of the version discussed below) is now available with full online management of the quiz. It works very well and very easy to create your own quiz's! See: http://deslimstemens.nu/

User Manual

  1. Download this package containing the original sources of Jonathan Huyghe + Excel template ‘DSM-Voorbereiding.xlt’
  2. Open the file ‘DSM-Voorbereiding’ with Excel and allow macros to run.EnableMacros
  3. Fill in all grey marked fields (names of players, questions, answers). The other fields are protected so no mistakes can be made by accident.
  4. Some questions require .jpg images or .flv flash movies. The names and resolution of the files are put next to the questions. These files should be saved in the same folder next to the DSM .swf flash file manually.
  5. Save the Excel sheet, it is advised to save it in the same folder as the DSM .swf file
  6. Click on the top button ‘Exporteer antwoorden.txt’ to generate (or overwrite) the ‘antwoorden.txt’ file (in the same folder as the Excel file) based on the data provided in the sheet. This file is required by the DSM .swf flash tool.
  7. Click on the top button ‘Print steekkaarten’ to get a print preview of the instruction cards that can be used by the game host. All instructions, questions and answers are clearly put together to print out and use during the play. The cards can be printed out 2 or 4 per page to make it easier to hold them during the game.


Examples


I've made two quizzes using the Excel file with questions, images and movies:


Extra



  • If some changes are required to customize the layout etc, the sheets can be unprotected (no password is used) using the Excel menus.
  • To convert movie files the freeware tools Format Factory and / or Riva FLV encoder can be used.
  • To convert / edit the picture files, the freeware tool Paint.NET can be used.
  • While exporting the 'antwoorden.txt' file, another file 'DSMData.txt' will be created (since v2.0). This 'DSMData.txt' file can be imported on a Windows Mobile pocket pc to get a very user friendly interface to control the flash quiz on a pc. For this, it will be required to install Salling Clicker on the PC and Windows Mobile device. Next this Salling Clicker 'De Slimste Mens' script needs to be added in Salling Clicker. A new item will be available in Salling Clicker on the Windows Mobile device. I've put some screenshots online.



(Dutch – Nederlands)

Jonathan Huyghe heeft een mooi flash programmaatje gemaakt om je thuis het populaire Vlaamse TV spel ‘De slimste mens ter wereld’ te laten spelen.
Omdat ik het nogal omslachtig vond om het spel op te zetten, heb ik een Excel template gemaakt om alle voorbereidingen te vergemakkelijken en wat duidelijker te maken. Deze Excel laat toe om het bestand ‘antwoorden.txt’ te genereren met de juiste syntax. Ook kunnen steekkaarten afgeprint worden met alle instructies, vragen en antwoorden die nodig zijn tijdens het spelen van het spel.

Update 01/2016: Een alternatieve versie voor het opzetten en spelen van eigen 'De Slimste Mens' (volledig losstaand van deze hierboven beschreven) is nu beschikbaar. Deze is volledig online beschikbaar. Deze kan volledig via de website http://deslimstemens.nu opgesteld en gespeeld worden. Dit werkt zeer vlot en hiervoor dienen dus ook de onderstaande instructies en Excel bewerkingen niet langer voor opgezet worden, volg gewoon de eenvoudige instructies op de website.

Handleiding:

  1. Download dit zip bestand met alle nodige bestanden van Jonathan Huyghe en de Excel template ‘DSM-Voorbereiding.xlt’.
  2. Open het bestand ‘DSM-Voorbereiding.xlt’ met Excel en sta het uitvoeren van macro’s toe.EnableMacros
  3. Vul al de grijze velden in (namen spelers, vragen, antwoorden). De andere velden zijn normaal beschermd zodat deze niet per ongeluk kunnen gewijzigd worden.
  4. Voor sommige vragen is het nodig om .jpg afbeeldingen en .flv flash filmpjes beschikbaar te maken. De namen en de resoluties die hiervoor moeten gebruikt worden, staan aangegeven naast de vragen. Deze bestanden moeten manueel in de zelfde map als het DSM .swf bestand geplaatst worden.
  5. Sla het Excel bestand op. Belangrijk hierbij: sla het bestand op als een Excel Macro-enabled bestand .xlsm, en niet als een standaard Excel .xlsx bestand, anders zullen de macro’s voor het exporteren verloren gaan. Het is aangeraden om het bestand in dezelfde map als het DSM .swf bestand op te slaan.
  6. Klik op de bovenste knop ‘Exporteer antwoorden.txt’ om het bestand ‘antwoorden.txt’ automatisch aan te maken (of te overschrijven). Dit bestand zal in dezelfde map als het Excel bestand geplaatst worden. Het bestand ‘antwoorden.txt’ is nodig voor de werking van het .swf programma DSM.
  7. Klik op de bovenste knop ‘Print steekkaarten’ om een print voorbeeld te krijgen van de steekkaarten. Deze steekkaarten kunnen tijdens het spel gebruikt worden en geven duidelijk de instructies, vragen en antwoorden aan. De steekkaarten kunnen per 2 of per 4 per blad afgedrukt worden om ze gemakkelijker vast te houden tijdens het spel. Eventueel kan hiervoor de gratis CutePDF printer gebruikt worden om naar PDF te printen met 2 of 4 per blad.
  8. Open het bestand ‘DSM3x4.swf’ met Internet Explorer om het spel te starten, volg vervolgens de instructies op de steekkaarten.

Voorbeelden


Zelf heb ik 2 quizzen gemaakt, met de Excel met vragen, afbeeldingen en filmpjes:

Extra




  • Als er wijzigen nodig zijn in de layout enz. kan het nodig zijn om de bescherming van de Excel tabbladen te verwijderen. Dit kan eenvoudig via de Excel menu’s (er is geen wachtwoord gebruikt in de beveiliging).
  • Voor het omzetten van de filmpjes kunnen de gratis applicaties Format Factory en / of Riva FLV encoder gebruikt worden.
  • Voor het omzetten / bewerken van afbeeldingen, kan de gratis applicatie Paint.NET gebruikt worden.
  • Bij het exporteren van het bestand 'antwoorden.txt', zal nu ook een bijkomend bestand 'DSMData.txt' aangemaakt worden. Dit bestand kan geïmporteerd worden op een Windows Mobile toestel om zo een zeer eenvoudige bediening van het volledige spel toe te laten. Hiervoor moet op de computer en de Windows Mobile PDA wel Salling Clicker geïnstalleerd worden en vervolgens moet dit Salling Clicker 'De Slimste Mens' script toegevoegd worden. Een nieuw item zal beschikbaar zijn in Salling Clicker op het Windows Mobile toestel. Enkele schermafbeeldingen.


Update 2/12/2010 v1.4: Extra validation added for field lengths. Status bar message for export added. Added import functionality.

Update 6/12/2010 v1.5: Added example quiz questions.

Update 17/01/2011 v2.0: Added export for Salling Clicker

Monday, November 29, 2010

Personal expense sheet

With the formula I presented in my previous blogpost on Excel look-ups I created an Excel sheet to monitor my personal expenses.
It is based on the data I extract from my different online banking systems (currently for the Belgian banks Argenta, Landbouwkrediet and KBC).


In the 'Categories' sheet, different keywords linked to the category name need to be filled in, this could be the name of a company or the account number. The keyword will be looked up in the account and comment of each transaction. And this will make it possible to automatically categorize each bank transaction and automate some analysis/summary overview on the transactions.


Every now and then, I copy the data from the online banking system into the 'data' Excel sheets. All expenses are categorized automatically with my formula in the column 'Auto Type', but if some exceptional category needs to be assigned, a 'Manual Type' column can be set to override the 'Auto Type'. Based on these categories, I created some sheets with an summary overview for each month and some averages per month and per year. This way, I can get a clean overview on our expenses and incomes and keep a copy of our banking data offline as well (since many banks only keep last 2 years online).



Based on my personal sheet I created this empty (dutch) template sheet, but it will still need some custom changes to be usable for someone else. But it could be a nice starting point.

Thursday, November 25, 2010

GIT usage (for an SVN user)

I put together some stuff about git I found interesting.

Installation


To get started with git on Windows, you'll probably want to download TortoiseGIT (and this requires msysgit)


Setup


To use git, a username and email need to be configured. This can be done in of TortoiseSVN -> Settings -> Git -> Config.


Migration from SVN


A very good manual to migrate your existing SVN repository to GIT can be found in this blog post of Jon Maddox.

Good to know


Git tracks content not files
Many revision control systems provide an 'add' command that tells the system to start tracking changes to a new file. Git's 'add' command does something simpler and more powerful: git add is used both for new and newly modified files, and in both cases it takes a snapshot of the given files and stages that content in the index, ready for inclusion in the next commit.


Most SCM systems use Delta Storage systems - they store the differences between one commit and the next. Git does not do this - it stores a snapshot of what all the files in your project look like in this tree structure each time you commit. This is a very important concept to understand when using Git.


There is only one Git Directory per project (as opposed to one per subdirectory like with SVN or CVS), and that directory is (by default, though not necessarily) '.git' in the root of your project.


Git is much faster than SVN.


In SVN, each file & folder can come from a different revision or branch. At first, it sounds nice to have this freedom. But what this actually means is that there is a million different ways for your local checkout to be completely screwed up.


You have to tell SVN whenever you move or delete something. Git will just figure it out.


Branches are cheap and easy to merge, so this is a good way to try something out.


A single git repository can maintain multiple branches of development. The 'master' branch is a default branch that was created for you automatically. The 'git checkout branchname' command will switch between branches. The command 'git merge branchname' will merge changes from another branch in the current active branch.


The 'pull' command performs two operations: it fetches changes from a remote branch, then merges them into the current branch.


'git commit' commits locally, whereas 'git push origin master' pushes the master branch to the remote named 'origin').


Git adds complexity. Two modes of creating repositories, checkout vs. clone, commit vs. push... You have to know which commands work locally and which work with "the server" (I'm assuming most people still like a central "master-repository").


Git is MUCH better suited if some developers are not always connected to the master repository.


Even if you don't have commit rights for a project, you can still have your own repository online, and publish 'push requests' for your patches. Everybody who likes your patches can pull them into their project, including the official maintainers.


Drawbacks of Git:


  • it's much harder to learn, because Git has more concepts and more commands.
    • many Git commands are cryptic, and error messages are very user-unfriendly
  • revisions don't have version numbers like in subversion
  • you have to have a full copy of the repository, you can't work on partials


Git branching model


A very good post on git branching model
Pdf model overview
  • Central repo with 'origin/master' and 'origin/development' branches (infinte lifetime)
    • 'origin/master' branch reflects the production release
  • Supporting branches
    • feature branch: branches from development and merges into development, branch name different from master, develop, release-* or hotfix-*, exist in developers repos only, not in central origin
    • release branch: branches from development and merges into development and master, branch name release-*, all feature branches must be merged in development before release branch is branched off
    • hotfix branch: branches from development and merges into development, master and release, branch name hotfix-*


Central Repo


If you like to setup a git central repository on Windows (for example to replace your VisualSVN environment), you can follow this manual.

Update 23/04/2015: A good tutorial and Git related info can be found on this Atlassian site.

Wednesday, November 24, 2010

MoWes Portable + OS Commerce 2.2 / 3.0a

I wanted to play with OS Commerce. Since this is written in PHP I needed some portable PHP, Apache and MySQL package. MoWes Portable does exactly what I was looking for and they offer some very nice online MoWes Mixer so anyone can easily create it’s own portable package (choice in version, packages, etc.). I created my own package with OSCommerce v2.2, v3.0a and phpMyAdmin. I added extra languages and applied some standard configuration/tweaks to make it immediately usable in Belgium.

Full package download containing MoWes Portable 2, Apache2_SE, MySQL5_SE, PHP52, MediaWiki, OpenDB, OS_Commerce 2.2, OS_Commerce 3.0a, phpBB3 and PHPMyAdmin.

VirtueMartVirtueMart is a similar open source project, but build within Joomla!. For me they seem very similar in the provided functionality. Joomla and thus VirtueMart can be integrated within MoWes as well using the MoWes mixer.

InstantRails + Redmine

I just discovered Redmine, and it looks quite promising to me as a project development tracking system.

InstantRails

Since I’m a big fan of portable applications, I tried to play with Redmine using InstantRails, but had some trouble getting it to run, since InstantRails uses some old versions of some required gems. So after upgrading gems to 1.3.7 and rack to 1.0.1 and finding some old version of the MySQL lib libmysql.dll v5.0 I got it all working nicely locally and portable. If anyone would like to play with it: download InstantRails 2.0 + Redmine 1.1.1 (42MB). I removed some parts of the gems documentation files to make it smaller.

The InstantRails package contains MySQL, Apache, Ruby and Rails. To use it, just run ‘InstantRails.exe’, the Apache and MySQL servers will automatically be launched.

 InstantRails

During startup, InstantRails may request to regenerate the configuration because of a folder location change. Just press ‘Yes’. If Apache doesn’t start automatically, make sure the ‘apache\logs’ folder exists.

Next click on the ‘i’ button, in the menu chose ‘Rails Applications’ –> ‘Manage Rails Applications’, select ‘Redmine’ push the button ‘Start with Mongrel’.

ManageRailsApplications

StartMongrel

A command window will open up and when it’s loaded you can navigate to http://localhost:3003 and start using your own personal portable Redmine. (default login: admin, password: admin). To manage the database, phpMyAdmin is included as well, just navigate to http://localhost/mysql.

If you don’t care to have it portable, you could as well install Ruby on Rails and Redmine using this 1 minute guide.

Plugins

Redmine 2 is included, but I added the following plugins I found interesting as well:

  • Codereview: code review comments can be added in repository within Redmine. It has a project tab with an overview of the code review comments as well
  • Hudson: project tab for Hudson administration
  • Issue due date: automatically set issue due date to the version due date
  • Timesheet: create a timesheet with overview of tracked timings
  • Time Tracker: easily track timings while working on issues
  • Wiki notes: extra wiki options, example created
  • Wiki tabs: possibility to add a project tab linked to wiki page, not configured yet
  • Tab: custom project tab configuration, configured to get the Sonar project information page

Demo

Within Redmine I configured roles, issues states, permissions and a workflow. I set up different users, projects and issues to be able to play with different configuration settings. Each user is configured to use the same password as the username: admin, teamleader, developer, etc. (see administration console). It is very easy to configure which role can perform what tasks and see specific content within Redmine.

Administration

Creation and management of issues is straightforward. It has a good integration of time tracking, at every update of an issue, the user can immediately update the time tracks.

UpdateIssue

With the time tracker plugin, it is even easier to track your timings. With an issues selected, you can easily let Redmine keep track of the time you spend on that issue by using the start/stop links at the top.

TimeTracker

Reporting

An important part of an issue tracking system is the reporting capabilities.

A summary with the number of issue in open or closed state can be seen:

Summary

In the list of issues, filters can quickly be added and columns can be chosen:

IssuesFilter

In the roadmap overview, all versions / planned version of the project are listed with an overview of their related issues:

Roadmap

The activity overview shows the activities performed within a project.

Activity

The spent time within a project can be viewed in an overview:

SpentTime

But custom reports can be created on the fly as well:

SpentTimeCustomReport

SpentTimeCustomReportFilters

With the Timesheet plugin, an overview of all projects, all users, etc. of the spent time can be generated easily on the fly:

Timesheet

Integration

Specific plugins exist to integrate the Redmine issue lists with TortoiseSVN and TortoiseGIT: Tortoise Redmine Plugin.

For full integration, follow this manual for the BugTraq properties configuration.

TortoiseIntegration

Points of improvement

One thing I noticed during my tests: if an issues is updated and a user changes the state of the issue, he still can select any user to assign it to. In my opinion, only users able to make changes on the selected target state of the issue, should appear in the list of users to assign the issue to. An issue tracking system should enforce all constraints of the workflow to make sure an issue can’t come in an invalid workflow state (eg. assigned user can not change the status). But this problem occurs on most tracking systems I checked (Track+, Jira, etc.). See this feature request, I checked the plugin development, but so far I couldn’t make it work myself yet.

Update 31/01/2011: InstantRedmine package updated to Redmin 1.1.1

Tuesday, November 2, 2010

Excel lookups in formulas

Different solutions exist to work with looked up data in Excel formulas: LOOKUP, VLOOKUP, HLOOKUP.

  • LOOKUP(value, lookup_range, result_range): searches for value in the lookup_range and returns the value in the result_range that is in the same position
  • VLOOKUP(value, table_array, index_number, not_exact_match ): searches for value in the left-most column of table_array and returns the value in the same row based on the index_number.
  • HLOOKUP(value, table_array, index_number, not_exact_match ): searches for value in the top row of table_array and returns the value in the same column based on the index_number.

But I needed yet some other lookup functionality, something like SEARCHHLOOKUP(search_in_text, lookup_range, result_range) where values from the lookup_range are searched in the text of search_in_text and if found the value of the result_range with the same column as were it was found is returned.

I wanted to be able to define some categories, with keywords linked. If a keyword occurs in a sentence, I wanted the category name as result. and I wanted to easily add new keywords for each category, without changing the formula. For example sheet CATEGORIES:

A B
Fruit Food
1 2
1 apple chocolate
2 banana milk






Next I have a cell with value: "Apple belongs to category" In another cell I want a formula (no VBS) that would result in the category name: "Fruit". For example sheet EXAMPLE:

A B
1 Apple belongs to category Fruit
2 Chocolate belongs to category Food




(Example SearchLookup.xlsm)

Since I need to search for the keyword in a sentence, I use the SEARCH(search_text, search_in_text, start_position ) function. If the result is bigger than 0, the keyword was found (not case sensitive). But if the keyword was not found, an error value #VALUE will be returned. To catch this error value, the function IFERROR(value, value_if_error) can be used. So I get this function: {=IFERROR(IF(SEARCH(CATEGORIES!$A$2:$A$5;EXAMPLE!$A1)>0;CATEGORIES!A$1);"")}

But this will only lookup keywords in my first column of Categories, while I want many more. I solved this by attaching a weight to each category and using the formula CHOOSE(position, value1, value2, ... value_n ) to select the category name corresponding it's weight. To ignore the error values when the keyword is not found, I return 0 if an error occurs and the category weight when the keyword was found, so a MAX on that array will result in the matching category weight. To make sure that 0 is returned, only when the keyword is not found, I add any single character (µ in this case) in front of the text to search in, else 0 could be returned if the text to search in starts with the text we are searching. Now the search has to be bigger than 1 when the keyword is found.

The result is this formula: {=CHOOSE(MAX(IFERROR(IF(SEARCH(Categories!$A$3:$A$6;"µ"&Example!$A1)>1;Categories!$A$2;0);0);IFERROR(IF(SEARCH(Categories!$B$3:$B$6;"µ"&Example!$A1)>1;Categories!$B$2;0);0))+1;"";Categories!$A$1;Categories!$B$1)}

Since we apply the SEARCH function on an array, the complete formula has to be an array formula, so don't forget to press CTRL+SHIFT+ENTER to save as an array function and get the "{=...}" signs around the formula.

So using a combination of CHOOSE, MAX, IFERROR, IF and SEARCH functions I can lookup category names base on keywords and the keywords can be added dynamically. The only "problem" left is that I need to change my formulas when a new category is added, but at least not when adding keywords in a category.

To solve this last problem, I ended up creating a 'User defined function' SEARCHHLOOKUP(search_in_text, lookup_range, result_range, (result_range_index)):

Function SearchHLookup(Search_in_text As Variant, Lookup_range As Range, Optional Result_range As Range, Optional Result_range_index As Integer)
'''''''''''''''''''''''''''''''''''''''
'Written by myT - http://myTselection.blogspot.com
'Values from the lookup_range are searched in the text of search_in_text
'If a match is found, the value of Result_range in the same column and top row (or result_range_index) is returned
'Example:
'A B
'1 2
'3 4
'if 2 or 4 is found in Search_in_text, B will be returned
'if 1 or 3 is found in Search_in_text, A will be returned
'if none is found, empty string will be returned
'''''''''''''''''''''''''''''''''''''''
Dim iRow, startRow As Integer
Dim iColumn, startColumn As Integer
If Result_range Is Nothing Then
startRow = 2
Else
startRow = 1
End If
startColumn = 1
For iColumn = startColumn To Lookup_range.Columns.Count
For iRow = startRow To Lookup_range.Rows.Count
If Not (Lookup_range(iRow, iColumn) = "") Then
If (InStr(1, Search_in_text, Lookup_range(iRow, iColumn), 1) > 0) Then
If Result_range Is Nothing Then
SearchHLookup = Lookup_range(1, iColumn)
ElseIf Not (Result_range_index = 0) Then
SearchHLookup = Result_range(Result_range_index, iColumn)
Else
SearchHLookup = Result_range(1, iColumn)
End If

Exit Function
End If
End If
Next iRow
Next iColumn
SearchHLookup = ""
End Function


So in my example, the function I use now has been simplified to:



=SearchHLookup(A1;Categories!$A$3:$B$4;Categories!$A$1:$B$1;1)



Of course, a SearchVLookup could be made easily as well:



Function SearchVLookup(Search_in_text As Variant, Lookup_range As Range, Optional Result_range As Range, Optional Result_range_index As Integer)
'''''''''''''''''''''''''''''''''''''''
'Written by myT - http://myTselection.blogspot.com
'Values from the lookup_range are searched in the text of search_in_text
'If a match is found, the value of Result_range in the same column and top row (or result_range_index) is returned
'Example:
'A 1 2
'B 3 4
'if 1 or 2 is found in Search_in_text, A will be returned
'if 3 or 4 is found in Search_in_text, B will be returned
'if none is found, empty string will be returned
'''''''''''''''''''''''''''''''''''''''
Dim iRow, startRow As Integer
Dim iColumn, startColumn As Integer
If Result_range Is Nothing Then
startColumn = 2
Else
startColumn = 1
End If
startRow = 1
For iRow = startRow To Lookup_range.Rows.Count
For iColumn = startColumn To Lookup_range.Columns.Count
If Not (Lookup_range(iRow, iColumn) = "") Then
If (InStr(1, Search_in_text, Lookup_range(iRow, iColumn), 1) > 0) Then
If Result_range Is Nothing Then
SearchVLookup = Lookup_range(iRow, 1)
ElseIf Not (Result_range_index = 0) Then
SearchVLookup = Result_range(iRow, Result_range_index)
Else
SearchVLookup = Result_range(iRow, 1)
End If

Exit Function
End If
End If
Next iColumn
Next iRow
SearchVLookup = ""
End Function


The user defined function needs to be defined in a module. Example: SearchLookup.xlsm)

Thursday, September 23, 2010

Userfriendly encryption (on USB sticks)


Previously, I always used FreeOTFE to encrypt personal files on my USB sticks. The problem is that I don't find the 'FreeOTFE Explorer' very user friendly and safe, since files need to be exported before you can open them. This 'FreeOTFE Explorer' is the only way to use a FreeOTFE encrypted container on a pc with no administrator rights.
So now I found a better little tool: Rohos Mini Drive.

  • free (official 2GB encrypted disk limited for freeware, but disks created with the free Rohos Disk Browser can be as large as you want)

  • easy to use, with AES 256bit encryption

  • unique on-the-fly encryption with no administrator rights, the disk browser allows working with files as regular (double click any file to open it immediately)


Download Rohos Min Drive 2,2MB.


When setting up a USB stick with Rohos, it will copy the 'Rohos Mini.exe' and 'Rohos Disk Browser' to your USB stick. You can always use the 'Rohos Mini.exe', if it detects you have currently no administrator rights, it will automatically open the Disk Browser application (located in a hidden folder [drive]:\_rohos\rbrowser.exe).



Btw, Rohos has some other very nice applications as well, like the Rohos Face Logon to login in Windows with webcam (or see a picture of people who tried to break into your computer)...

Sunday, March 28, 2010

Exam point counter (Excel)

To help counting points during correction of the exams, I made some Excel VBS. When the exams are corrected, next to every error a -0,5 -1 or -1,5 is written, and these have to be substracted from the total points of every part. To make it possible to perform this little task one handed, I linked the 'h' key to -0,5, the 'j' key to -1 and the 'k' key to -1,5. Now the exams can be run through very fast and the result of every part is shown immediately. To keep track of the total points of every part for every student, I linked the 'x' key to save the result in a sheet with the totals. Finally, the 'c' key is linked to clear the contens of a column with all substractions of a part of the exam.


The exepected usage of the sheet:



  • fill in the totals of every part of the exam in the top most row of the first sheet (called 'Punten teller'/'Point counter').

  • (optional) if the maximum points in a part is different from the weight of that part in the complete exam, the second row in the first sheet can be used. The points will be recalculated to match the weight.

  • (optional) fill in the name of every part, for example: excercises, theory, vocabulary, etc.

  • select the cell in the fifth row of the first column an run through the exam. For each part of the exam, a next column should be used

  • while running through a part of the exam, use the keys 'h', 'j' and 'k' for every mistake in the exam, respectively substracting 0,5 1 or 1,5 points

  • when a part of the exam is finished, you can see the total points in the third row of that column

  • if you want to keep that result in the totals list, press the 'x' key, the statusbar will clearly show the changes applied in the totals list (so you don't need to switch the sheets every time to verify)

  • if you want to recount the points of that part again, press the 'c' key to clear the data of that column (exam part), without updating the totals list

  • if you want to clear the column (part of exam) and go to the next part, use the 'n' key

  • when every part is done, the points of each part will be saved in the 'Totaal'/'Total' sheet for every student, and the total is converted to a % point

  • (optional) for every student, a name can be added in the second column of the 'Totaal'/'Total' sheet

  • the total point of every part are rounded as for example 6.0; 6.1; 6.2 go to 6, 6.3,6.4,6.5,6.6 and 6.7 go to 6.5 and 6.8 and 6.9 go to 7

  • (optional) comments for the exam of every student can be added in the 'Totaal'/'Total' sheet (last column)

  • to get a nice overview of results of every student in a separate document (vakrapport/course report), a word template using Words build in MailMerge is created. It can be used in combination with a sheet based on this Excel template. The template can be updated, the result will be a separate page with points, median and comments for every individual student (a hidden sheet 'MailMerge' in the Exel template is used for this). To get best results using this template, it is advised to fill in the topics in this list marked as '(optional)'


Excel sheet template (english version, dutch 2003 version, english 2003 version) (when opening a new sheet will be created, so the template will always be kept intact)


29/03/2010: Updated template, english and 2003 versions added


04/04/2010: Updated template, added vakrapport template