Share your data tips and tricks:

[NormalDistribution]

This is a thread to just put those little things you find out that can help make efficiencies / provide enlightenment.

 

Can be absolutely anything...

 

I'll start with an excel tip I use a fair bit:

 

If you want to find and/or replace A* in a sheet of data

Then enter search term for A~*

This will find only the A*

This works in formulas as well like COUNT, SUM ETC.

 

So whilst =countif(A1:A99,"A*") would count all the A's and A*'s in a column (because * is a wildcard) =countif(A1:A99,"A~*") would count just the A*s.

[LosOjos]

If you have a lot of VLOOKUPs to do between sheets in Excel, it's a good idea to use the MATCH() formula to locate which column number a particular field is in. This helps prevent those annoying miscalculations that happen when it comes time to add a new column and suddenly your VLOOKUPs are all looking in the wrong place!

 

Similarly, using named ranges in Excel will make updating those formulae a whole lot easier.

[vikpaw]

I replace A* with @ . This allows easier finding of the grades, also if you want to compare grades in Excel, you can put in code(<CellRef>) and this gives you a number so you do subtractions and see that an A is two above a C. This works because the @ symbol is 064 and an A is 065. Quite a nice trick.

I actually, make @ appear in the SIMS gradeset and on marksheets for ease of entry, because we didn't used to show the exam grade on reports, and use numeric grades internally. This year they want to show the mock exam grade, and now i need to fiddle with report output, so will be looking at @losojos' guide on sticking a macro in to find and replace that value at runtime. Unless someone has a better option. i need it to replace the value on a bulk print, so just working when you open or preview won't work.

[LosOjos]

vikpaw - easy way around that, set the "Description" of the grade to the human readable form (i.e. A*) and use "ResultDescription" on the individual report

[number34]

I find it easier is to use A* as the grade. Does not cause any problems in SIMS and I don't bother doing any analysis in Excel.....

[LosOjos]

A "trick" I found recently in SIMS - if any of your departments record raw scores for coursework, mock exams etc. and need the UMS equivalent in SIMS, provided you know the conversion ranges you can perform this by creating lookup tables.

 

For instance, our English department record the raw score for various AQA modules in SIMS throughout the year. Using AQA's UMS calculator, I have created lookup tables for these modules. Set the age step to 24 months (the maximum) and make sure you cover all ages in your school (I use 10/00 to 20/00). The maximum mark should be the top level of the raw score. To make this even easier, you can recreate the table in Excel and copy/paste it back in to SIMS (allows you to copy/paste the same values in to all column, rather than having to manually type them all in SIMS!)

 

You can also use lookup tables to convert grades to rudimentary values, allowing you to accurately calculate the VA for a grade (e.g. rather than A*=38, A=32 etc. you can perform a lookup to convert them to A*=8, A=7 etc. without touching the associated gradeset)

 

Examples:

 

[alexhulse2]

(Trick nicked from SupportNet but a good one!)

 

Useful trick if you have a column in AM7 that could contain a grade but could also contain a blank, but you want to be able to run if then else or nested if then else against it reliably.

 

If you create a formula column called Zeroes with EQUALS with 0 as the constant, you can then create a further column that SUMs the Zeroes column and the grades column and it will give you the value of the column if there's a grade, or a nice reliable 0 if there's nothing.

 

I've found that ITE or NITE can be fussy about blanks and produce odd results, so being able to compare with an actual 0 can be useful.