How to check for blank values in SIMS marksheets
I've found a way to spot blank values in marksheets on SIMS using a formula, so that it's possible to set a column value if another is blank. Finally...
There are a couple of different ways of doing it, but the basic principle is the SUM formula type in SIMS will ignore blank columns and still return a value, if at least one of the columns is populated. The most robust method works a bit like this:
Col 1 - Aspect Value (data entry, data review or formula entry)
Col 2 - PLUS formula (review), adding 0.5 to Col 1
Col 3 - DIFFERENCE formula (review), subtracting 0.5 from Col 1
Col 4 - DIFFERENCE formula (review), subtracting Col 2 from Col 3
Col 5 - EQUALS formula (review), set to constant value 1
Col 6 - SUM formula (review, or you can store it if you want), adding Col 4 and Col 5
Col 6 will be set to 0 if Col 1 has a value, or 1 if Col 1 is blank. You can even colour the empty cells if you want, or whatever.
Obviously, Cols 2 - 5 probably want to be hidden. I've uploaded an XML file with templates showing how to do this onto SupportNet - if you use SIMS and don't go there, you should! (Sivadam will like that :p )
There are a couple of ways to shrink the number of columns used:
If you reverse the order of operands in Col 2 (to change the sign of the result), then Col 4 can be skipped, with col 6 summing up the values of Col2, Col3 and the constant column
If you want to check completion of lots and lots of aspects, you can just sum the "Col 2 and Col 3" values as two groups, adding the constant to each group, and then taking the difference will give you the number of columns that have been completed!
Hope this helps someone out there - I keep on hearing it said (and saying myself) that "there's no way to check for blanks in SIMS", but now I think differently!