How to check for blank values in SIMS marksheets

[MattMitchell]

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 )

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!

[ChrisH]

Sounds like a lot of messing around especially if you have a variable amount of columns in your different marksheets, but I suppose once it's setup it's done and it is a way I never would have thought of doing it. I would be tempted to export them and run a macro over them that gave you a log of missing information.

[penfold_99]

If you save the value of column 6 in a aspect you can then create a report to list the missing data by staff.

That would be very handy to for the peron who is responible to data collection.

[vikpaw]

V.nice, but it's pretty taxing to do for each column of data entry. We ask for 5/6/7 grades at a time sometimes, and it needs putting on each marksheet...?
If you add it to a large summary marksheet it's not easy to see who it refers to.
I still favour having a chain of command where someone superior (head of year/dept) is responsible for making sure the data is in.
They can just open a marksheet, order by a column and see where the blanks are. usually if one column is blank the adjacent ones haven't been filled for some reason as well.
Or am i missing root of the problem..?

[CAM]

Every student should have a target. So if you have given all students a target, you will have a value in your summary rows at the bottom saying how many Targets there are for that subject (I put the subject target next to the subject's data entry column in my report marksheets). If the total count of collected grades at the bottom is less then the number of targets, I know to look for blank cells in the column.

[ChrisH]

I assume there is some request to make this kind of thing a proper feature somewhere on support net? We shouldn't really have to be going to these lengths.