How to count cells coloured by conditional format in Excel

Another non moodle post.  C'est la vie.

Quite a few questions pop up on Google saying they have a spreadsheet that uses conditional formatting to colour cells and they want to be able to count how many cells of a certain colour they have.  All the answers seem to point to this post by C Pearson who has an answer but it's not super obvious what to do with it.

If your spreadsheet press Alt and F11 to open the visual basic editor.  This allows you to add function to Excel.  In the left hand column right click the bold heading, it should say VBA Project(name of your spreadsheet).  Go to Insert > Module.

In the window that opens copy and paste the function code C Pearson provides and click the save icon. Then do the same right click, Insert > Module and do the next block of code. You need to add the Active Conditioning one and then whichever of the others you think would be useful to you.  I did all of them so I could try them out and the used CountOfCF.

So once you've added all the functions, use the formula:

=CountOfCF(C11:S15,3)

The green numbers are the range of cells you are looking at and the red number is the number that corresponds to the colour you're counting.  For me this number is three because it is the third condition in the conditional formatting.

And there you have it, counting conditionally formatted cells.

Adding one user to multiple groups via bulk upload

So I've got a nice women from a council buying 300 courses for nurseries in her area.  She wants to able to keep an eye on them and see how many have completed and how many are being lazy and not doin' their learnins.

I posted before about using groups for enrolment keys and creating a new role with the ability to run reports.  The trouble with using these two methods together is that the person with the special role (I called it Local Authority) needs to be in all the groups that correspond with the enrolment keys she's giving out.  This means  I have to add my council lady to three hundred groups and there isn't just an easy way of doing it in one go.

The way around it is to use the bulk user upload option.  You can upload a CSV file to create new users, enrol them on a course, put them in a group and give them a non default role.  Now my council lady already has a user account so I don't need to create that or give her a password.

I started a spread sheet with these column headings:

username

firstname

lastname

email

course1

role1

group1

I fill in the next line with the existing accounts username, first name, last name and email address.  Then I copy and paste that line into the next 299 rows.  The three hundred enrolment keys are distributed across three courses, one hundred in each.  So in the course1 column, the first hundred rows are the short name of the first course.  I thought at first it would be the course ID but it is the short name you need.  The next hundred rows are the second course and the third hundred rows are the third course.  This will enrol her on the courses as a student.

Under role1 I put the special role short name, in may case it's LA.  Under group1 I copy and pasted the group names from the spreadsheet I used to upload groups to each course.

Then I saved this as a CSV and uploaded it under the Site Administration > Users > Accounts > Upload Users option. When you click upload you'll be taken to a page with a few options.  On this page, depending on your situation, change Upload Type to 'Add new and update existing users' This means it will update the existing account with the new courses, role and groups rather than trying to create the same account a bunch of times.

Hit Upload Users and the job should be done.  If you check enrolled uses on a course the account should have been added to it with the new role and put in all the groups.  So now my council lady can see the activity of everyone that uses one of her enrolment keys.

Mahara as an assignment

If you have got your Mahoodle up and running you can set a Mahara page as an assignment submission.  I intend to use this so I can set a group of learners a bunch of pages with text boxes in, which they can fill in and then have assessed.

I followed the instructions in this wiki but, as with everything Moodle related, it's not completely clear.

There are two Moodle plugins you need to install.  First download the local folder. Unzip this and you'll get a file called moodle21. Unzip this as well and you'll have a file called pax_global_header and a folder called mahara-contrib-local-mahara.  You need to upload the contents of the folder to your moodle in the /local/mahara directory (you might need to create the /mahara folder).

Next you need to download the assignment type plugin. Unzip this and you'll get another moodle21 file which  you also need to unzip.  Again you'll get a file and a folder.  Upload the contents of the folder to your moodle in the mod/assignment/type/mahara directory (again you might need to create the /mahara folder).


Login to your moodle as an admin and visit the notifications page.  Click refresh to complete the installation.


Still as an admin go to site administration > networking > peers > all hosts then click on the services tab.  You should see a new paragraph headed Extra Mahara MNet Module.  You need to click the subscribe box.  From what I understand, ticking the publish box makes no difference so I clicked it too.


If you go into any course you should now be able to set an assignment with an option to submit a Mahara page.

Assign an oversight role

Say the manager of a nursery school buys training courses for all the staff.  We issue enrolment keys, the manager distributes them and sits back happy to let the staff get on with it.  Three weeks later the manager wants to see how far the staff have got.

As an Admin create a new role.  Go to Settings > Site Admin > Users > Permissions > Define roles.  Click Add a new role.  Give the role a name (e.g. Monitor), short name and a description if you want.  In Role Archetype choose what role you want the new role to be based on.  I went with none-editing teacher.  In Context I chose Course, this means the role can be assigned at the course level.

Now go through the permissions and allow any that you want this new role to have.  This took a bit of trial and error for me but I pretty much allowed all reports and logs to be run and allowed them to see participants.

With the new role created you need the person to have an account.  You can do this for them or make them do it themselves.

Go to the course you want the person to be able to monitor.  This is the course they have bought for their staff, if they bought more than one you'll have to do this for each course they have staff enrolled on.  Go to Settings  Course Administration > Users > Enrolled Users.  At the top click Enrol Users.  In the Assign roles drop down choose the new role you created, find the person you want to give monitor rights to on the list and click enrol.  This will allow then to run the reports and see the participants of this course.

I've used separate groups so I can issue individual enrolment keys, these groups are all separate so users can't see each other.  This means that the new monitor won't see them either so we have to ass the monitor to all the groups their staff are in.  Refresh the Enrolled Users page of the course you're in and find the new monitor user.  Click the little plus sign in the groups column and add them to all the same groups as their staff, (bare in mind some of the enrolment keys they bought may not have been activated yet so add them to every group that corresponds with an enrolment key they purchased).  They will now be able to see everyone in the same groups that they are in.

Create new Local Authority role
Have the LA sign up like normal.in the course
In the course go enrolled users
Click Enrol users at the top.
Enrol the LA as the new Local Authority role.
Go to enrolled users
Click the plus sign Enrol that LA in all the relevant groups.
They will now be able to see everyone in those groups and when they last logged in.

How to make a check mark/tick in InDesign

I'll start by saying this has nothing to do with Moodle.

I have been designing book covers and one of them needed a tick mark.  So of course I did some Googling to try and find out how to do it.  I came across numerous posts from people trying to do the same thing and the answers they received varied from "It can't be done" to "It can be done but it's a massive hassle."

I eventually found the answer in the most unlikely of places, a Microsoft Excel help document.  This pointed out that Wingdings has a tick mark as one of its characters.

This is only for Windows by the way, I have no idea what to do on a Mac.

Got to Start > All Programs > Accessories > System Tools and choose Character Map.  In the font drop down choose Wingdings.  Now you can see all the handy little icons that make up Wingdings.  Click the one you want to use (the ticks are at the end) and press Select then Copy.  This will put the character on your clipboard.  Return to InDesign (or whatever program you're using) and paste the character in place.  The character will probably appear as a box because the font you're using doesn't recognise it.  Highlight the character and change its font to Wingdings and there you go.

I went one step further and made the tick mark into a bullet point. Create a new paragraph style for the list you want ticks next to.  In the Bullets and Numbering tab and click Add in the Bullet Character box.  Choose Wingdings from the Font Family drop down, click the tick mark you want.  This mark will now act as a bullet point for this list.

I'm not gonna lie, it was an utter revelation when I realised Wingdings actually had a use.

How to install a Magento extension

For those just joining us, I'm now in the process of setting up a Magento store to integrate with Moodle.

Magento is an e-commerce solution in a similar vein to Moodle, it is free and open source or there are people that will do the dirty work for you.  I will eventually be using Magento to sell Moodle courses but first I have to get Magento to work with our online payment gateway.

Magento has native support for Paypal but we're using World Pay so I have to install a plugin.

First we vist www.magentocommerce.com/magento-connect/ and search for the extension you want, you can choose to look at free or premium extensions.  Clicking on the nice big Install Now button.

Now, this might bring up a dropdown menu for you to choose Magento Connect 1.0 or 2.0.  I'll admit this means nothing to me and if you're reading this post it might not mean anything to you either.  In another tab or window log in to your Magento backend via the admin panel and go to System > Magento Connect > Magento Connect Manager, you'll be asked to log in again.  You'll be taken to the Magento Downloader and if you scroll to the bottom you'll see what version of Magento Connect Manager you're working with.  Mine says 1.5.0.0.

Now I assumed this meant I should choose Magento Connect 1.0 from the drop down on the extension page, but when I did it didn't work.  So I tried choosing 2.0 and that seemed to do the trick.

Choose whichever you think might work, tick the box to agree to the licence agreement and click get extension key.  Copy the code it brings up, paste it into the box on the Magento Downloader marked Paste extension key to install and click Install.

At this point it'll either work or return an error message.  If you get an error message (like I did) try the other extension key.  If it works, return to your Magento dash, see if you have any messages saying a cache needs refreshing and configure your new plugin.  In my case this mean going to System > Configuration > payment methods.

Adding terms and conditions check box to sign up page

Want your users to agree to your terms and conditions before they can create an account? No sweat.

As an admin go to Settings > Site administration > Security > Site policies.  Scroll down to Site Policy URL.  In the box but the URL of wherever your terms and conditions are  hosted online, this can be on your Moodle site or somewhere else if you have a company wide policy.

This puts in a link to the terms and condition and a check box that a new users must check before they can signup.

Simples.

Enrolment Periods

We decided we want learners to have access to their courses for 60 days.  Their username will continue to be recognised for access to Mahara and site wide message boards but they will only have 60 day to access and complete paid content.

In Course Administration > Users > Enrolment Methods > Self Enrolment you cans elect how long the enrolment period lasts.  After this time if a user tries to access the course they just get a message saying "You cannot enrol yourself on this course"  This is because when an enrolment period is defined, Moodle sets a start and end date for their enrolment but doesn't actually un-enrol them from the course, if you look at enrolled users they are still there.

Say, somebody completes a course and gets their certificate and, two years later we email them to say the course has been updated to meet new legislation and they want to do the course again.  They can't access it even if they pay for it again because Moodle thinks they are already enrolled.

You can manually unenroll them or edit the unenroll date to give them access again but it's easier to set an un-enrol if inactive (on the same page as the enrolment period setting).  I've set it for 60 days (at least as long as the initial enrolment period).  That way if they complete the course and then don't look at it again they will be un-enrolled after 60 days, so they can re-enrol further down the line.

I've used the word enrol too many times.

Apple's e-textbook and What it Means to Me

In case you haven't heard Apple announced a couple of education innovations.  iBooks 2, an iPad app for reading digital textbooks, and iBooks Author, a Mac app for creating digital textbooks.  Apple is also adding a way for teachers to use their iPad/iPod/iPhone to create full online courses including quizzes and assignments (that sounds familiar).  All this is being aimed at the high school level.

From my point of view the most interesting part of all this is the authoring tool.  I recently had a meeting with an AV company who said one major university they work with was giving up Moodle after three years because the staff just couldn't grasp it.  I can understand this, it took me months to get comfortable with Moodle and I'm still not an expert, and I don't have a full teaching schedule to work around.  If Apple can make the process of creating digital electronic content easier then it could speed up the rate of adoption.

However, as I've found trying to develop courses, simply giving creators the ability to include games and video does not necessarily mean they are going to to produce more effective learning material.  Likewise, putting a lesson on a screen does not mean that learners will automatically be more interested.

I feel like there is still something missing from elearning.  For me there has yet to be that lightbulb moment where something clicks and I think "This is the way forward."  I don't, as yet, see the massive benefit of elearning over conventional methods and I think anybody who has had a particularly inspirational teacher will agree with me.  There is massive potential and definite advantages for different learning styles and requirements but I don't think any of those will replace a genuinely good teacher.

And how many genuinely good teachers will have to be let go so a school can afford to buy every student an iPad?

That's the main drawback to all this technology and I think it will be Apples's undoing.  My high school couldn't afford to buy enough white paint for the year, I don't think I ever once saw a textbook that was less than five years old and they definitely couldn't afford to buy everyone an iPad.  Money is tight, schools can't afford the wi-fi to support all this elearning never mind the gadgets to do it on.

Until every school has the infrastructure to support every child in the country logging on at 9 am, or the mobile broadband network is strong enough, then using internet based resources is infeasible.

An app that only creates content for iPads is too restrictive.  If schools can't afford to buy them for the students that content has to be available for the technology already in children's hands and for every 14 year old that owns an iPad or iPhone there are three that have a Blackberry and ten that have a PC.  Content has to be borderless.

Competitors are only too eager to ride Apple's coattails and they will be only too happy to learn from Apple's mistakes and create a better system for content creation if that is what the market place wants.

And that is the main benefit to Apple's desire to get into education.  They have stirred up interest, they have got people talking and they will create a desire for elearning innovation.  I just don't think they'll be responsible for it.

Luckily I know someone who has recently got into open source elearning and content creation.  So thank you Apple, you have just ensured my employment for the rest of my life.