[sims] Data manipulation

[bhsman]

Hi

 

Looking for a bit of guidance on an issue I have.

 

As part of my role I have to take export a large marksheet into excel and manipulate the data. This involves breaking down the spreadsheet by gender etc and adding a no of quite exact formulas to a large no of columns.

 

My issue is this I have to do this half termly and it takes a great deal of time, plus there are a number of opportunities for errors to occur. For example if after manipulating the data I discover an error on the orig marksheet I have to start all over again! ( sometime the errors are not of my making ) So, to speed up/ Automate the operation, bearing in mind the number of students can alter between each data capture period, is there a way I can set up some sort of template in excel ( and I have to do it in Excel as SIMS itself cannot produce the degree of data manipulation required by the SLT), where I could copy varying number of students in but the formulas would remain and the various sorts and gender breakdowns would be done automatically? Am I asking to much of Excel? Would it invole some form of coding? Hve I posted this in the wrong Forum?

 

Regards

 

Bhsman

[Steve21]

That's all doable yes (obviously depends on exact details).

 

If you're only after things like you mentioned, it shouldn't be difficult. In terms of counting, you can set formulas to count "occurances" of things. aka M/F's

 

Sorts are easy enough too, I'd personally do it as a excel workbook with VBA enabled, so yes some sort of coding as you word it

 

Obviously you'd need to set some limit, but say you could count occurences within 1000 rows or w/e, to give you room for expanding in future

 

(And yes it's an excel/scripting question, rather than SIMS )

 

Steve

[pcstru]

It sounds as if it should be possible to set up the worksheet so that you import (paste) the data into one tab (worksheet) and have other worksheets reference that tab for their data. If the source data changes or you spot an error and so need to correct it in SIMS and then re-export, you then only need to past the new data into the raw data worksheet and the rest of the workbook should update from that.

 

If you have a variable amount of rows in the source data and are then somewhere up the line using sums/averages, you will probably run into errors (where intermediate cells are referencing blank rows on the source data sheet). You can use ISERROR() function on the intermediate cells to make sure that if there is no source data, the cells are blank rather than showing an error. Blank cells then won't much up sum, average, counts etc so your forumulas will be able to deal with a variable amount of rows/columns in the source data.

[vikpaw]

Vba is a good option but more complex. I went the second option and just paste data in and it sorts it out.

Also in the background we have a data assistant putting formulas into SIMS so that the bits it can manage are done internally. It's painful but only needs doing once - until management change the goalposts that is!

[dhicks]

Am I asking to much of Excel?

 

In my experience, it might not be Excel so much as other users - Excel does, after all, contain facilities to do an awful lot of interesting things, but you do need to know how to use it properly. You also have to know your statistics properly. If you have other people trying to use the spreadsheet to interpret results, change figures and so on then you'll have chaos. This kind of thing sucked up three summer holidays worth of time at my last job, time which could have been used to do useful things with our network. I tried setting up an Excel spreadhseet for users to enter figures in to but they simply never got the hang of it, so I then tried developing a few quick scripts to take a few basic CSV files of data and spew out some graphs. As these worked they started to get horribly extended in scope, so we started to have a whole growing data-analysis application cobbled together with no coherent structure.

 

My guess is that your best bet is to simply buy in a statistical analysis package and learn how to use it - Crystal Reports might be a good place to start. Otherwise, I'm guessing that a script that takes in an Excel spreadhseet of raw results and outputs an Excel spreadhseet containing sorted, eparated results and formulas might be your best bet - all you then have to do is enter results in the correct format in a single Excel spreadsheet.

[vikpaw]

I don't know the costs of their products and services and this does not mean it's okay to start advertising them, but you may like to look at a third party that can help such as this : 4Matrix