Thursday, 7 November 2013

Speaking computer

Thanks to this tweet:

I was reminded of an issue that causes problems. Computers speak a different language and it's difficult to get your head around at first.

If I told you:

"Pick a random number between one and thirty."

Would you have any problems reaching a number that fit those criteria? Probably not.

If you asked a computer to complete the same function it could take a very long time because it would see:

"Pick a random number." 33
"Is that number between 1 and 30?" No
"Pick a random number." 1958372
"Is that number between 1 and 30?" No
"Pick a random number." -8
"Is that number between 1 and 30?" No
"Pick a random number." 987362514037
"Is that number between 1 and 30?" No
"Pick a random number." 3304
"Is that number between 1 and 30?" No
"Pick a random number." -2837595093
"Is that number between 1 and 30?" No
... and so it goes until we reach the point where a number fits both criteria

And that is the difference between us and machines. Pick a random number is an instruction to us, and we await the criteria.

Pick is the instruction to a machine. A random number is the principle criteria. Between 1 and 30 is only the final check in the list.

When computers were being developed, the people who found it easiest to understand this language were the same group who developed programmes. The interfaces were then developed by this same group and adhered to those grammatical structures. Remember how difficult it was to use your first computer? And remember how the person showing you kept saying it was really easy?

It is deeply frustrating to anyone new to the word processing environment to be told something like "Select the text, then click bold" because actually, despite what you and I have learned, it is naturally more intuitive to us to choose the Bold state, then apply it to several parts of the text (a brush that paints things bold).

Thankfully, over the last umpty ump years GUI development has been carried out with people who don't understand the language of computers in an effort to make it more intuitive.

It does leave us in an odd position though, because those long standing operations aren't showing signs of disappearing - they're still common when using computers (how many people have never written a rich text blog, article, email, letter, etc?) but unlike the interactions that have only recently become mainstream (taking and editing video) they are not intuitive and have to be learned.

Doesn't anyone else think that's a bit weird?

Wednesday, 28 August 2013

Tablets in Learning

I'm reaching that most difficult biannual event... It's time to upgrade my mobile phone.

It took a single covetous glance around the offerings available in my local O2 store to convince me that I had an immediate problem. There are some features and functions that are only available in Windows Phones, and other apps that I rely on that are not available within the Windows store. So which do I choose?

And why is this relevant to my title?

You can almost guarantee that students coming into FE will already have exposure to tablet devices. In this sense they will have a preferred device, and applications of their own that they rely on. As an educational institute we need to be prepared for a huge variety of devices. Logically, we also need to avoid relying on apps that aren't available on all devices.

So what is the alternative? Many people seem to think that the apps available are the be all and end all of tablet devices. In reality, you can get a massive amount of use from a tablet without installing anything onto it by taking advantage of a browser and cloud products.

So from a technical point of view an educational institute can offer an infrastructure which allows students to use their preferred device.

But how does using any device benefit learning? There are three key factors in learning which are enhanced by tablet or mobile devices:

  • Communication
  • Participation
  • Enjoyment


I chose this image (left) as it illustrates a very clear memory I have of being a student. The phrase "Please be quiet, you're here to learn" cropped up more than once. At the time, I bought into it as a self evident truth, that for me to absorb information, I had to shut up and let the teacher talk.

That isn't actually an ideal circumstance for learning. Communication of information is essential for the learning process - if you never see any new information you will never need to learn anything new - but it is equally important that a teacher knows that their students are able to keep up with the lesson. We need a two way communication channel, and tablets are ideal facilitators of that.

I had to "shut up and learn" because the class was large and the teacher couldn't reasonably facilitate a discussion where every member made a contribution without significant disruption. However, if you consider a circumstance where each student has access to a tablet/ mobile device, you can quite easily have an open communication channel where students post questions and thoughts, which the teacher can follow up appropriately without causing any significant disruption (via twitter, VLE forums, mentimeter, etc). In fact, the anonymity of such feedback has been found to encourage students to admit they do not understand.

While information communication is one of the first stages in the learning process, experience or application of the information is required to cement it. In other words, active participation. In the traditional classroom, students are given exercises to work through. If these are printed out, everyone receives the same problem, and everyone reaches the same solution. This is ideal for the teacher as they can immediately pinpoint who has failed the exercise, but it is not good for the students, who maybe copied their neighbours answer, or need to experience the same problem three or four times with different variables before they can understand how it works.

Here there are various websites or tools that can be embedded within a VLE to generate random problems for students and grade them all immediately. This still gives the teacher the information they need - who has answered correctly, but it requires the student to work alone, and gives those who need it the opportunity to experience the problem repeatedly.

Lastly, enjoyment. I remember my physics GCSE very clearly. For the five minutes before the lesson we stood outside the lab, waiting to be allowed in and my friends and I ate an entire packet of Polo mints between the four of us. The sugar rush was the only thing that kept us awake. I don't remember the content of most of my physics lessons, but I do remember the plot lines and characters from the hundreds of books that I read at the time. (Sadly, I am very bad with titles and authors, so tracking these books down to read them over again is a little tricky.) Humans in general respond to challenges. Most people, especially younger people, have a degree of curiosity which drives them to ask questions, seek solutions and earn rewards. The whole gaming industry is built on this premise and we can employ similar tactics in improving their learning experience.

In summary: any implementation of tablet devices to improve learning must be carried out with the specific intention of targeting the learning process. Simply having tablet devices is not enough. Since we have to be able to respond to changes in use over time, and the trends that young people adopt, confining ourselves to a specific device is not the ideal solution. We want to create a situation where the technology can evolve, the gadgets can change, but the tools we use stay consistent.[1]

To ensure that the tools we use improve learning, we need to apply good pedagogy to their implementation. Therefore, I recommend developing methods and processes where tablet devices are used in a teaching environment to enhance communication, allow participation and foster enjoyment of the learning process.

Alicia

[1]This blog, for instance, is being written on a Mac. No one would ever have known that, if I had kept it to myself. Usually it is written on a Windows laptop, and occasionally on a Samsung S3. The device isn't the important thing. What matters is the tool, in this case Blogger, and how I use it.


Thursday, 1 August 2013

Access Databases

It's been a while since I posted due to the inevitable nose to the grind the end of the academic year brings.

However, I'm now on the other side of that particular war zone and have a new problem on my hands. It's a ridiculously simple one, but this is my first Access 2010 experience and the Macro/ Expressions/ Code setup is messing with my understanding of this database.

So, I have a master form on which is contained a drop down table of Providers. There is a button to add a new Provider if the one required is not on the list. The button opens a form using a macro which is prepared to Add a new entry into the Provider table.

So far, so good.

There is a "close" button on the Add a New Provider form, which closes the form using a macro. However, on returning to the master form, the combo box of Providers has not updated.

Now, I have tried a number of things but what finally worked was adding a Requery event to the onClose event of the second form.

So, make sure you are looking at the Properties of the Form. Go to onClose. Choose "Code" builder. In the VB window type:

Forms!Form_Name!Control_Name.Requery

This will add the new value to the Combo Box. I haven't found a way to get a Macro to do this (yet).

The other thing I want to achieve is that as the second form is closed, the combo box has automatically selected the new Provider that has just been added to the Provider table.

This, which seems to me to be a basic common sense function, is causing me some serious problems.

Alicia

Thursday, 25 April 2013

Pivot in MS SQL

In response to the previous post - cast nText to varchar mid-query.


select  pvt.[48] as slideContent
, pvt.[49] as link
, pvt.[51] as startDate
, pvt.[52] as endDate
, pvt.[53] as setPriority
from (select 
recordid
, fieldid
, convert(varchar(8000),content) as myContent
from Moodle.dbo.mdl_data_content
join moodle.dbo.mdl_data_records on mdl_data_content.recordid = mdl_data_records.id
where dataid = 12 and approved = 1) as ps

PIVOT
(
min(myContent)
for fieldid in
([48], [49], [51],[52],[53])
)as pvt

NOTES: DataID and pvt[x] needs to be updated to correct values for live table, but otherwise this query is exactly what is required to extract the full dataset.

Additional development required - limit the returned data to display ONLY those records where startDate < Now < End Date


Pivots in mssql

This is not a completely thought out post, it's simply an attempt to get that part of my brain that processes information to work.

I've created a Moodle Database activity which only teachers can access. They will submit material which will then be pulled by a function within Moodle to create a slider on my Moodle Home page.

The information submitted to the database can be extracted using:

select *
from mdl_data_records
join mdl_data_contents on mdl_data_records.id = recordid
where dataid = 12 and approved = 1

That returns several rows for each submission to the database and this needs to be restructured. A simplified overview:

recordid | field | content
58 | 49 | This is a text submission
58 | 50 | http://www.link.url
58 | 51 | 1366888911
58 | 52 | 1369888911
58 | 53 | 3 - Priority Normal

This needs to be pivoted as follows:

Database | slideContent | Link | StartDate | EndDate | Priority
58 | This is a text submission | http://www.link.url 1366888911 1369888911 | 3 - Priority Normal

However, the values in content here are nText, and they need to be comparable.

That's where I got stuck. Will have lunch and hopefully a blood rush to the brain will give me the answers.

Monday, 25 March 2013

Squirrels

Last night as I was falling asleep I remembered a story from years ago about squirrel researchers.

The primary researcher and his/her assistant set up a series of challenges for squirrels. The initial estimate was that it would take a week for a group of squirrels to "learn" the challenges and complete them easily. Once they were set up, the primary went to get coffee on the assumption that they probably wouldn't see a squirrel for half an hour. S/He returned within twenty minutes to be greeted by the gobsmacked assistant who had not only seen the first squirrel attempt the challenges, but watched it complete the whole collection in the time it had taken the primary to collect coffee.

Anyone who doubts the brilliance of a motivated squirrel can check this video, on a similar theme.

Squirrels have very few motivations, really. They want to be physically comfortable, warm and well fed. They also want their offspring to survive to maturity. The challenges that we witness squirrels mastering are always to meet one of these motivations. What is astonishing is the problem solving ability squirrels have, when the motivation is strong enough.

Teenagers want to be warm, comfortable and well fed. As social creatures they also want the respect of their peers and the more ambitious of them have a goal for their future - career aspirations, or personal goals.

What we insist of students in the UK is that they stay in education until they are 18, that they study to the highest level they are capable of and that they leave the education system with basic qualifications.

Our demands, or expectations if you prefer, don't actually meet up with any of the primary motivators of the average teenager. Other than those with a specific ambition that requires an education, what incentives are there to the students to stay in education?

I was lucky: both my parents trained as teachers and knew the value of education. I didn't have to push myself, because they managed to convince my subconscious that education is something I should have and keep trying to get more of. I didn't have a goal, or driving force to perform well at school. I did it because my parents expected me to and I didn't have any sufficiently strong motivation to drive me in any other direction. It wasn't my goal, my dream or my motivation. I think it is telling that my only A* GCSE was the one that I particularly chose (Art) - the others were either mandatory, or the best of an uninteresting lot.

But what about everyone else? We tell them they must get good GCSEs to get good ALevels. They must get good ALevels to get a good job or go to university. They must get a good degree to get a good job. But, when you think about it, those are all very abstract concepts - especially to someone who has never had the responsibility of working so that they can feed themselves at the end of the day, or pay rent, or bills. The squirrels won't complete an obstacle course in order to qualify for a mortgage and likewise our teenagers aren't in a position to understand the value of what is on offer.

We either need to give them benefit of three years of life and work experience before offering them education, so they can see what it's all about, or offer them a more immediate motivator to enable them to really commit to their own improvement.

Friday, 15 March 2013

Mahoodle

I am delighted to be looking at ePortfolios right now.

I'd like to finish my queries that I was having so much trouble with, but every time I look at a database I feel myself getting dizzy and confused.

So, I'm looking at the portfolio options enabled within Moodle.

I was slightly surprised to see the list of default options as it's rather more extensive than I was expecting:


Portfolio plugin

I've used some of these in various ways (although I'm intrigued by "File Download") and I know they all have their own benefits.

However, I think I'm going to succumb to hype - I really like the look of Mahara after a cursory examination, but the thing I genuinely like about it is the option for students to take ownership. The problem we're hearing is that students aren't particularly comfortable with Moodle and therefore don't take to it. 

It is possible that even if they aren't comfortable with Mahara to begin with, the freedom they have to customise it to suit themselves may give them the incentive to continue.

Thursday, 7 March 2013

Why handwritten exams?

ALevel students, as I'm sure many of you know, have exams at the end of the year. Our current education minister Michael Gove is actually pushing for the whole assessment of their education to be a single end-of-year paper. Because the papers are handwritten by the students, throughout the year they have to complete exercises that involve them writing large quantities to ensure they are prepared for an exam at the end of the year.

Because of this, we have to keep printing out papers for students to complete.

So is there an alternative? Well, we could certainly insist that every young person has a tablet device that is compatible with writing responses to an exam paper pdf, but given that more than a quarter of people live in "low-income households" it is difficult to justify pushing that additional cost onto them. Similarly, as the budget is continuously chipped away from education, it is more than slightly intimidating to consider the cost of issuing every student a tablet device for the forseeable future.

But that's only if the exam papers remain handwritten. What about the other possibility? Why do the exam papers have to be handwritten? Can that be changed?

Well, I have given this some thought, and I think there is at least one viable alternative.

Have you heard of the Raspberry Pi? It's a computer, about the size of a credit card, and costs £15. It's developed by a British charity and well worth looking into.

Now, imagine if, instead of printing and mailing papers, the exam boards put a copy of their paper onto the Pi, and mailed one per student out to each participating college? The college would plug the Pis in, attach a monitor, keyboard and mouse, and the student could type their responses as they would in every other aspect of life.

In terms of security: Each student would register on the device with their student ID, in the same way they fill out the front of the exam paper. We could easily have the Pis locked until a specific time, so the exam only became accessible when it was supposed to be. And once the exam boards receive all the Pis they could upload all the papers to their own storage (as some already do) prior to marking, then wipe and reset the Pis for reuse the following year.

This way, we don't have to keep training students in handwriting, and exam boards save a fortune (and many forests) in paper costs. And, as the Pi is smaller and lighter than an exam paper, they would arguably save on postage too.

Wednesday, 6 March 2013

(Failing at) Understanding the Moodle database 4

This is deeply frustrating. I'm building up a sketch of how I think this is constructed and I can't find the missing piece. So:

On Moodle, we have courses. There is a table (mdl_course) which contains the basic construction details  for each course and assigns an id value.

On Moodle we have users. There is a table (mdl_users) which contains the basic details about each user and assigns an id value.

There are various contexts on Moodle in which people can be enrolled. These may be courses, categories, etc. Context Levels don't appear to be defined in the database but can be tracked down in the docs, which may or may not be obsolete (?)

However, for the purposes of this: Users are enrolled on Courses. The contextlevel for courses is 50.

Each context is described in a table (mdl_context) which gives each context an ID, describes the contextlevel, the depth it occurs at (system, category, sub-category^nth, course, resource) and the instanceid. In this table, instance id refers to the specific id of the item. ie, instanceid 474 occurs twice in my table - once for a user and once for a course.

There are various ways people can be enrolled on a course. The methods in which people are or may be enrolled are listed in a table (mdl_enrol) which describes an enrolment method, related to a courseid and states if it is currently allowed. It also describes a sort order for these methods and an id for each position in the table.

There are various roles users can have within courses and across the site. The different roles are described in  a table (mdl_role) and given an id.

There is a table (mdl_role_assignments) which assigns a roleid to each userid with a specific contextid.

So, in role_assignments, for a random userid there is an entry:

id   | roleid | contextid | userid | component
2169 | 5      | 3611      | 1034   |

Then, in context we can look for id 3611 and see


id   | contextlevel | instanceid | path | depth
3611 | 50           | 152        |      | 4

We know from the context level that this is a course, with instance id 152


So, lets look in mdl_course for id 152


id  | category | Course name and other details -->
152 | 49       | 

So we know the user, the course they are enrolled on and the role they have in that course. However, when I did my count, not all the students were listed. If we look in mdl_enrol, we can see this course only allows manual enrollments. 


So lets look at course 588. This allows manual, category and two meta enrollments. We'll try to track down the students in meta enrollment id 2926:


id   | enrol | status | courseid 
2926 | meta  | 0      | 588

so in mdl_context lets look for contextlevel = 50 and instanceid = 588



id    | contextlevel | instanceid | path | depth
10662 | 50           | 588        |      | 4

and in mdl_role_assignments lets check out contextid 10662



id   | roleid | contextid | userid | component
3835 | 3      | 10662     | 21     | enrol_meta
3836 | 4      | 10662     | 405    | enrol_meta
3837 | 5      | 10662     | 46     | enrol_meta 
3838 | 5      | 10662     | 48     | enrol_meta
...

and I get 14 entries. 1 teacher, 1 non-editing teacher and 12 students.

EDIT: see, that's not actually helpful. I just checked the course and of the two meta linked courses one has 44 and the other 77 students.


There must, somewhere in one of these tables be an enrolid column, surely? Where I can look up mdl_enrol.id = 2926 (and 2925, which was the other option)



There is, in mdl_user_enrolments a column called enrolid, so I'll just look into that... There are 131 rows for 2926 and 133 for 2925. While that sounds more promising a little cross checking shows that there is too high a percentage of people who are not enrolled on this course listed here. Additionally, apart from the two additional userids in 2925, both lists are identical.

id    | status | enrolid | userid
46550 | 0      | 2926    | 17
46551 | 0      | 2926    | 18

Lunch break, I think.

EDIT (a couple of days later!): 


select
count (distinct mdl_user_enrolments.userid) as 'Students'
, mdl_context.instanceid as 'Course ID'
, mdl_course.fullname as 'Course Name'
from [moodle].[dbo].[mdl_role_assignments]
join [moodle].[dbo].mdl_context on mdl_role_assignments.contextid = mdl_context.id
join [moodle].[dbo].mdl_course on mdl_context.instanceid = mdl_course.id
join [moodle].[dbo].mdl_enrol on mdl_course.id = mdl_enrol.courseid
join [moodle].[dbo].mdl_user on mdl_role_assignments.userid = mdl_user.id
join [moodle].[dbo].mdl_user_enrolments on mdl_enrol.id = mdl_user_enrolments.enrolid

where mdl_context.contextlevel = '50'
group by mdl_context.instanceid, mdl_course.fullname
order by [Course Name] asc

This query counts people enrolled on a course. It is not just students - but this is because a lot of my students do not have a specific role assigned to them - if you want to count students only, include "and mdl_role_assignments.roleid = '5'". The difficulty I was having was due to stupidity on my part (I was crosschecking on the wrong set of data :( )

Monday, 4 March 2013

Understanding Moodle database 3

Great news!

Getting the course name out (previously all reports were unit names, although in Moodle they are courses, so it gets a bit confusing) is actually very simple.

In mdl_course there is a category value and in mdl_course_categories we can identify the category name from the associated id.

So in my first query I added two new lines and changed the order:

select
userid
, mdl_context.instanceid as 'CourseID'
, mdl_course_categories.name as 'Category'
, mdl_course.fullname as 'CourseName'
, mdl_user.email as 'email'
from [moodle].[dbo].[mdl_role_assignments]
join [moodle].[dbo].[mdl_context] on mdl_role_assignments.contextid = mdl_context.id
join [Moodle].[dbo].mdl_course on mdl_context.instanceid = mdl_course.id
join [Moodle].[dbo].mdl_user on mdl_role_assignments.userid = mdl_user.id
join [Moodle].[dbo].mdl_course_categories on mdl_course.category = mdl_course_categories.id
where mdl_context.contextlevel = '50' and roleid = '3' or roleid = '4' order by Category asc

C'est ca!

Understanding the Moodle database 2

Following on from my previous: here I am counting the number of students enrolled in each course.

Nice and simple this one :)


select
COUNT (userid) as 'Students'
, mdl_context.instanceid as 'CourseID'
, mdl_course.fullname as 'CourseName'
from [moodle].[dbo].[mdl_role_assignments]
join [moodle].[dbo].[mdl_context] on mdl_role_assignments.contextid = mdl_context.id
join [Moodle].[dbo].mdl_course on mdl_context.instanceid = mdl_course.id
join [Moodle].[dbo].mdl_user on mdl_role_assignments.userid = mdl_user.id
where mdl_context.contextlevel = '50' and roleid = '5'
group by mdl_context.instanceid, mdl_course.fullname
order by CourseName asc

No new tables here, just a slightly different way of extracting the data.

We're counting the number of students in each course, which requires the other columns to be grouped (count is an aggregate function, you have to describe how you want the other columns to be arranged). Note - you have to use the actual column name in the Group by, you can't get away with the renamed value. I learned that the hard way :)

roleid for students is 5 - although forum recommendation is that you should use the short name rather than the id value as this is a customisable setting.

And now, I'm going to see if I can combine this query with the previous one and get one big table of results :)

EDIT: BIG PROBLEMS IN "SIMPLE" QUERY TOWN

So, a little further inspection of the returned results demonstrated that my query only returns those users enrolled directly onto the courses. The users who are enrolled via a meta link are not counted. I haven't tracked down the correct table for the meta linking yet and have actually opened a Moodle forum query about it because I am *stumped*.

I have a looming suspicion that cohort enrolment may also be wrongly counted, but I'm having trouble finding a Cohorts table with up to date information. There should be two cohorts for each ALevel we deliver and myriad others for the short courses/ complimentary courses available. Unfortunately, there are only 5 cohorts  in the helpfully named mdl_cohorts table, so I don't think that's correct.

*sigh* going crosseyed. Ideally I'd break at this point and think about something else for ten minutes, but all of my typical resources have a tenuous link to what I'm doing here and I can't stop myself consciously reflecting on it.

Understanding the Moodle database

I am trying to produce a report that tells us for every course on our Moodle site:

Name of course
Name of unit
Names of teachers on unit
Number of resources/ activities in each unit
Range of resources/ activities in each unit
Number of students enrolled on unit
Number of active students on unit
Number of hits on unit between September 3rd 2012 and June 22nd 2013
Number of hits on each resource/ activity between September 3rd 2012 and June 22nd 2013

Now, the Moodle database structure is not clear at a casual glance so writing up the queries is an interesting challenge and (when I come back to this next year) I will inevitable forget the reasons behind the structure of my queries so, query by query, here's a quick wherefore;


select
userid
, mdl_context.instanceid as 'CourseID'
, mdl_course.fullname as 'CourseName'
, mdl_user.email as 'email'
from [moodle].[dbo].[mdl_role_assignments]
join [moodle].[dbo].[mdl_context] on mdl_role_assignments.contextid = mdl_context.id
join [Moodle].[dbo].mdl_course on mdl_context.instanceid = mdl_course.id
join [Moodle].[dbo].mdl_user on mdl_role_assignments.userid = mdl_user.id
where mdl_context.contextlevel = '50' and roleid = '3' or roleid = '4' order by CourseName asc

Teacher Names on Course.

mdl_role lists the different roles and assigns each an id. RoleId 3 & 4 are Teacher and non-editing teacher respectively.
mdl_role_assignments contains information about each role assigned to a specific user in each context (system, category, course, forum, etc). It contains the column roleid referred to in this query, context and userid.
mdl_contextlevel contains the different context levels. There is no description here of what the context levels are but it is explained on this page (not got time to figure out how to link to the exact response, but it's there!)
mdl_context gives the id of each item with enrolment (I'm pretty sure of this, but could be wrong), the contextlevel related to it, the instanceid (course ID) related to it and the path and depth (path = /system/category(^nth)/course)

Now for a lunch break and to see how much sense this makes on my return.

Thursday, 28 February 2013

Blended Learning

Just a thought I've been wrestling with...

It's well observed that students who are motivated to take responsibility for their own learning perform best.

It's also quite noticeable that by posting content onto a VLE we are pushing that responsibility onto students.

So how do we then manage it? How can we switch the mindset of students to being willingly responsible instead of sullenly responsible?

I have no answers here; just questions.

Monday, 18 February 2013

Count Distinct equivalent in Excel

Sometimes MicroSoft try to gently tell me off for using the wrong product to do a job.
Sometimes I respond in a very adolescent fashion: "But that's not FAIR! It's not my fault!"
This, sad to say, is one of those times.
I have been requested to generate a report describing how many accesses each of our ALevel courses have had since the start of September. I don't have access to the SQL databases - this was a choice made by the IT team which I can understand and appreciate as if I knacker it, they have to repair it. That being said, it causes me grief at times like this.
The alternative method is to use the reports system in Moodle.
  •  In Logs you can produce a report of all activity in a course on a specific day, or over the whole of time. 
  •  In Course Participation, you can look back over a specific period of time, but only for each individual resource or activity in the course, not the whole course at once.
We have 75 courses making up our ALevels (they've been broken down into units, and there are some Enrichment programmes included - we do not deliver 75 ALevel subjects!) so I really want to do as little processing as possible.
I used the Logs option and exported the data to Excel sheets (alternative options: print to page, print to ODS, download as text).
At this point I should probably have imported the data from the spreadsheets into a database, but I did not. This was partially because I need to be able to distribute the data and while many people here understand the layout and structure of a spreadsheet, they do not all feel as comfortable with databases.
Instead, I combined all the downloaded sheets into a workbook, deleted everything older than September 1st 2012 and removed all instances of my name (my presence skews the data quite badly).
My next action explains the purpose of this post: I wanted to count the total number of hits in each course which is fairly simple (=counta(SheetName!D:D) - Note I wanted it to count everything in the column as columns are variable height. I will need to subtract one to remove the column headings, but no biggie) and I also wanted to count the number of users who had accessed the course.
This was more complicated - if I was using SQL I could simply CountDistinct but MicroSoft in their wisdom, have not made it that simple.
However, there is a very elegant mathematical solution:
=SUMPRODUCT((DataRange<>"")/COUNTIF(DataRange,DataRange&""))
Isn't it lovely?
In summary, it checks the data range for duplicate values (discounting blank cells as non-values) and creates an array describing the number of times each item appears. Each of these is then converted to a fractional value in a beautifully cunning twist so that the sum of all their occurances is equal to 1. The total of these is then taken to give the number of distinct values.
Now, I owe my thanks to one of the ALevel Maths teachers for sending me an example of almost exactly this, but I'm not sure where he got it from.
However, for a much better explanation of how it works, check out: here and here
There is still a lot to be done in terms of pointing to the correct data range 75 times, but it simply adds an incentive to learn the VBA to do that job for me.

Wednesday, 13 February 2013

Sporcle embedded

You know, it always pleases me when I find people are willing to share their stuff.

I've been a Sporcle fan for a while, but only recently looked into embedding their content on my sites.

Next step - Moodle!