Sometimes I hear an announcement about an update or improvement to some technology or ‘app’ (as the youngsters say) that sounds exciting and eminently worth investigating.  When I start sifting through Google results to find out more, I can spend a day or two sometimes working out what’s going on.  That’s OK, it’s part of my job to do this and then, if what I’ve learned is useful, to find ways to communicate it to other people.

For example, Excel now allows users to create ad hoc and custom functions using lambda().  Now, if you are a computer scientist, mathematician, philosopher or linguist, you will probably have heard of the lambda calculus, an important mathematical invention of the twentieth century that influenced all those disciplines.  It provided a way to formally characterise computation as function application and abstraction (roughly, don’t quote me on this – it was a long time ago).  Now, even students of computer science may sometimes encounter the calculus and end up wondering ‘OK, but what’s it for?’

Well, one way to demonsrate its practical use is by introducing the world of the lambda() function in Excel: it allows you to formally define new Excel functions.  I immediately spotted some  uses  for this.

Descriptive statistics for sub-populations

Excel provides all the most common and useful statistical calculations as basic functions such as count(), average(), var(), stdev() and for some functions there are conditional versions countif(), averageif(),which allow for subsetting your data.  So, it might be that you have two columns of data, the first is some interesting measure (temperature? height? resting bpm?) and the second some characteristic such as ethnicity or gender.

It might be that you want to know what is the average resting heart rate (for example) of the male participants in  your study.  You can do this using averageif(): =averageif(B1:B100, “male”, C1:C100).  Assuming that the sex data are in the range B1:B100 and the resting heart rate data are in C1:100.  In fact, Excel has an averageifs() to help with cases with multiple selection criteria.  So that’s good for data analysts using Excel because it’s a common analytical approach.

However, this only works for averageif(), countif(), maxif(), minif() – there is no conditional var(), stdev(), skew() or kurt().  Well, for variance and standard deviation, we can construct a pivot table to get the subpopulation analysis we want and that’s great.  But occasionally (probably not that often) we want to calculate the skew in resting heart rate for all male participants, and maybe even the kurtosis.  And here, after that long lead in, is where the lambda() function proves it’s worth.  First, lets look at how we would caculate the kurtosis in some measure as a function of gender, using a combination of built-in Excel formulas: Given a data table where column A is the categorical (eg gender) variable and B is the measure of interest:

The formula we want is kurt(if(A1:A17=1,B1:B17))

So there are three parameters, the first is the range to which we apply the second (the selection criterion value (here, 1 = male)), the third is the range from which this conditional statement will select data.  I tested this formula and it works fine, it’s just a bit clunky to use compared to the built-in averageif() etc.  So, I decided to reconstruct it using the lambda() function, to produce my own kurtif() function.  In an empty cell, we put the lambda expression:

=LAMBDA(a,n,b, KURT(IF(a = n, b)))

with the three parameters represented (arbitrarily) by a, n and b.  A moment’s reflection and we see the relation between the lambda expression and our previous Excel formula.  From a practical point of view, the lambda expression tells us where in the calculation to plug in the values a, n and b to get our result.

When you enter this expression in a blank cell and hit return you will see the warning message

which is Excel recognising that the cell contains a lambda expression.  The next step is to name the new function kurtif().  Copy the new lambda expression to the clipboard (highlight and control-c) then from the formula tab on the ribbon select and open the name manager.  In the dialog that this opens press the new button; give kurtif as the name for the new function and then paste the lambda expression from the clipboard then OK and close the name manager dialog.

Now you can carry out the computation as:

=kurtif(A1:17,1,B1:B17)

which is simpler and has the advantage of looking very like averageif() and the other, similar functions.

A missing significance calculation in Excel

Excel has a very simple to use function that will calculate a correlation coefficient (R) from two arrays of data: =correl(Array1,Array2).  It is a minor annoyance that this calculation doesn’t return a p value for R, allowing us to test the null hypothesis that the true correlation between Array1 and Array2 is zero.  So, let’s assume that we have calculated R for two arrays of data, and we know that n is just the count of one array, and the result is in cell H1 (for no particular reason).

Now, it’s a fairly simple trick to calculate a t value based on the correlation coefficient.  The formula is

and since we have just calculated r, it is simple to calculate t with the formula (and put the result in H3):

=H1*(sqrt(count(Array1)-1))/sqrt(1*-H1^2)

The last (and for now separate) step is to find the significance for this t score with the two-tailed t distribution function with n-2 degrees of freedom

=t.dist.2t(H3, count(Array1)-2)

And there we have it.  So, it would be useful to have a little helper function we could apply simply,  to calculate  t  from r.  Here is the lambda function code:

=LAMBDA(r,n,(r*SQRT(n-2))/SQRT(1-(r^2)))

and we can name it and use it as before.  I named mine ‘convertRtoT’ and used it like this with the correlation coefficient in H1 and n = 30:

convertRtoT(H1,30)

So now we know what the lambda() function is for.  The example is perhaps a little obscure, but the principle – that half the struggle with digital knowledge is knowing what it’s for – holds for far more mundane cases: I’ve been learning Power BI, and while there are simple answers – power BI is for visualisation – it’s only after I’ve been through a few hours of tutorials that I’m really understanding what it’s about.

Homework

If you want to check out the example calculations, please go ahead.  I checked them all in Excel (and for the t to r conversion, I checked my result against R), but it’s always possible to make a bluder when copying and pasting.  But finding and fixing errors is good practice.  If you want more practice, then I would suggest creating a function skewif() that works like kurtif(), taking two arrays ( a score and a selection criterion) a gives the skewness for the cases selected by the criterion.  Good luck.

(this blog post was supported by the music of Iannis Xenakis, “Six Chansons No 1, ca sent le musc”)

The Discovery Tool consists of reflective question sets which explore your digital practices and confidence levels.  The main question set looks at overall digital capabilities but there are also additional question sets for staff covering effective teaching and accessibility and inclusion and leadership all from a digital perspective.   The tool can also be used by students and there is a general question set for all students and one aimed at new students.

See below for full details of how to use the tool and upcoming workshops on 23 April and 13 May.

New for 2024 there is now a question set on Digital Skills in AI and Generative AI for staff and a similar question set will be available for students on 20 March 2024.

The staff AI questions are arranged under the following categories:

We currently have online workshops scheduled to take place at 15 February and 6 March 2024.

About the workshop

The Digital Skills Development team in ISD run a series of three Excel Essential Skills workshops designed to ensure that they do.  These cover creating calculated worksheets, data management and using in-built Excel functions.  They are facilitated by fellow students in small groups of 10 – 12 in computer workrooms on campus.

The workshops take a new approach to learning by presenting students with a problem to be solved and encouraging them to use prior experience, web searches, in-application help and fellow participants to find a solution to the task.

By the end of the session students have improved problem-solving skills, an increased knowledge of the topic, a ‘suggested solution’ to the problem they worked on and some resources and guidance for further learning.

Students take the workshop at their own pace so they can leave when they have completed all the tasks and either finish the tasks in their own time or come back to another session if they don’t have time to complete.

Why not share details of our upcoming workshops with your students and encourage them to attend? Places on these workshops can be booked here:

• DSD: Excel Essential Skills – WORKSHOP 1
  Upcoming sessions: 15 February  12 pm, 10 May 11 am
• DSD: Excel Essential Skills – WORKSHOP 2
  Upcoming sessions: 21 February 12 pm, 1 March 12 pm, 24 May 11 am
• DSD: Excel Essential Skills – WORKSHOP 3
  Upcoming sessions: 7 March 12 pm, 7 June 11 am

To see our full schedule of courses for this term, see our most recent newsletter.

Alternatively, bring the Excel Essential Skills workshops to you!

We can arrange workshops specifically for your cohort and our materials are also available for you to offer these workshops yourself, facilitated by you or your teaching assistants.   Please contact us isd-digiskills@ucl.ac.uk if you are interested in either of these options.

Do you want to try something new in your teaching or assessment?  Are you concerned that your materials aren’t as accessible and inclusive as you would like them to be?  Why not come along to one of our short online* training sessions to learn a new skill, improve your teaching materials or explore new assessment methods?

Add more interaction to your course by using polling software like Mentimeter for Continuous Module Dialogue or to engage students either during class or asynchronously between sessions.  You could also Use online whiteboards to encourage student interaction or ask students to collaborate on projects using Microsoft Teams: get a Beyond Basics Overview or learn about Document Management and Collaboration.

Digital Accessibility is a vital part of making your course inclusive and we have courses on Enhancing the Accessibility of your Moodle Courses, Creating accessible PowerPoint Presentations  and  Creating accessible Word documents.  You might also want to ensure you are following Best practice for video captions and transcripts when sharing multimedia.

Liven up your course with more multimedia content by learning how to do Basic video editing using iMovie on a Mac or Record a narration over your PowerPoint on a PC to convert your  presentation to video.  You could also come on our Podcasting made easy workshop or our series of workshops for making a film using your iPhone (Planning, Filming advice and Make a short film) and pass these skills on to your students so they can create multimedia content as part of their course.

If graphical content is a challenge, our beginners courses in Basic image editing using free tools can get you started with existing content while An introduction to free graphic design tools and Creating Infographics using free web-based tools are a great way to start creating more original content without having to buy expensive software.

Finally, if you’ve never done Digital book-making, it can be great for digital storytelling and Sway, Microsoft’s modern presentation tool is another interesting option for presenting your material in new ways.  These can also open up new possibilities for your students and the ways in which they present their coursework.

We’ve cherry picked a few courses below that might be of particular interest, but for our full schedule of upcoming courses visit Digital Skills Development News on the Digital Education blog.  If you subscribe to the blog you’ll get regular updates from us and our other Digital Education teams. Click on the link(s) below to book a place or add yourself to the interest list to be informed about upcoming dates.  More details about how to book, how to join sessions etc. can be found on our Practical Course Information page.  If you aren’t sure what training you need and would like individual support and advice we also have a regular drop in during term time.

*All sessions are online via Microsoft Teams unless otherwise indicated.

Add more interaction and collaboration

Make your content more accessible

Use more audio-visual content

Create and edit graphical content

Present material in new ways

Please note that the above dates and times were correct at time of publication but are subject to change.  Follow the links provided or check our latest schedule for the definitive current schedule.

Unless you’ve been living under a rock for the last year or so (well ok, lockdowns did feel a bit like that sometimes) you probably know what a ‘hybrid’ meeting is. Just in case, though, it’s a meeting where some of the participants are in the room and some are joining via a digital platform like Teams or Zoom and are therefore ‘remote’.   

One school of thought is that you should avoid hybrid completely, but realistically it’s going to happen, especially now we are gradually going back into the office and most of the people attending a meeting may well be in the same physical location.  In this case it would be absurd for them all to join remotely from their separate desks when they could be sat in a room together.   

So how do you ‘do hybrid’ well, so that everyone feels included? 

Disclaimer: Note that this guidance is focused on meetings rather than teaching, which is covered in Steve Rowett’s very comprehensive blog post on Basic hybrid teaching in UCL spaces.

Meeting preparation 

Wherever possible, share resources such as presentations or documents beforehand.  This is good practice for meetings anyway and makes them more inclusive and accessible for everyone.  Make use of collaborative platforms such as Teams to start a discussion and get input prior to the meeting.  

Technology 

Getting the technology right is key.  For the richest experience, everyone in the room should join via Teams or Zoom, not just the remote participants, so that everyone can be seen on camera.  However, it is vital that there is only one microphone and one set of speakers in use to avoid the dreaded feedback cacophony!  If the number of people in the room is small and you are quite close together, you can usually just use the audio equipment on one person’s laptop, the obvious person being the chair of the meeting.  If the meeting is bigger or people are very far apart, you can use a conference speaker, such as those made by Jabra (other brands are available).  Practise the setup in advance of the meeting and check people can be heard at the furthest distance from the microphone.   Some UCL meeting rooms also have room cameras, microphones and speakers which can be used and some are even ‘Teams enabled’ so the room itself can join the Teams meeting.

It probably goes without saying, but don’t forget to share your screen for remote participants.  It’s easy to forget this, especially if you are using a large monitor or projection to share your screen in the room. If you want to share ideas using a whiteboard or post-its then opt for a digital interactive whiteboard for everyone to use.

Starting the meeting 

• Set clear expectations of what is expected from people at the start of the meeting.  How should people indicate they wish to speak? Are you using the chat and if so, for what?  If everyone is on a device you can ask everyone to use the raise hand feature in Teams/Zoom.  Another option is for people to actually raise their hand or use both, particularly if not all remote participants are visible on camera.  Make sure that the people in the room know to speak loudly and clearly. 

• Do introductions if it is a first meeting of the group or membership has changed.  This can help everyone to feel included in the meeting. 
• Encourage everyone to participate and to let you know straight away if there is something they can’t see, hear or understand due to being remote. 

Including remote participants 

It’s important to make a special effort to make remote participants feel included.  Depending on the size and nature of the meeting and the balance of remote and in room participants, you may find the following helpful: 

• Have a ‘co-pilot’ who can read out any questions or feedback in the chat and alert the chair to anyone who wishes to speak or if anyone is unable to hear something being said. 

• Call on remote participants by name to check if there is anything they wish to add to the conversation, especially if the matter being discussed is particularly relevant to them.  You may want to ask them first, so they don’t feel like an afterthought. 
• Describe what is happening in the room if remote participants won’t be able to see it.  You may also need to repeat some of what is said in the room or say who is speaking if they are not on camera.  Even things that are irrelevant to the main business of the meeting (like someone fumbling around with cables to connect to the projector) should be explained so that remote participants don’t feel left out.   
• Be aware of what remote participants see. Even if everyone in the room is on camera, remote participants can’t see where people are in relation to each other or other objects in the room. Non-verbal interactions between people or pointing to something in the room probably won’t make sense to remote participants.  Make a point of looking at the web cam especially when you are speaking to help make remote participants feel connected. 
• Set aside a bit of social time, either at the beginning or the end of the meeting, for participants to chat to each other.  Traditional meetings where all the participants are in the room usually have a few minutes where people are arriving and can say hello to each other before the meeting begins so find ways to incorporate this into your hybrid meeting too.  Starting meeting a few minutes after the hour can give people time to have a comfort break, move between rooms etc. but also allow for this social element to happen. 

Most importantly, be ready to adapt and change as you go along and find what works best for you.  Expect a few hiccups to begin with, but in the long run hybrid meetings can give you the best of both worlds so it’s worth persevering!