Below is a presentation for the Carl-Benz Academy on May 20th, 2015.
Improved Data Wrangling and Validation in Excel
Presented November, ’14 at CAIR
Are your Excel spreadsheets error-free? This presentation is intended to help you use automated processes to clean up and transform data in Excel. The overall approach combines vLookup, conditional formatting, named cells and ranges, formatting for meaning, and spatial layout. The resulting approach reduces errors, increases auditability, and speeds up repetitive tasks. Virtually all Excel audits find errors; this presentation is designed to increase your confidence in data wrangling tasks.
More than just getting input, we used focus groups to build a policy, grow support, and fast-track implementation. For our small tuition-dependent university, admissions standards are a politically charged topic. A small team began by creating data-driven admission criteria. We then used focus groups to engage with stakeholders campus-wide. Each focus group began with a brief presentation, and then quickly transitioned into a guided dialogue. The meetings allowed the team to find and address points of opposition. By the time the proposal went to the full faculty body and administration, most of the potential critics had already been engaged. This session will present the focus group methodology used to build consensus.
Excel’s & (ampersand) symbol is powerful tool for combining columns. For example, you can
- Display names in different ways. Eg., “John” in A1 and “Smith” in B1 can be shown as
- John Smith by =A1 & ” ” & B1
- Smith, John by =B1 & “, ” & A1
- Making a mini-mailmerge for copying & pasting student grades into an email (see video).
- Named fields help with this. Instead of =”Hello, how are you today ” & A1 & “?” , write =Greet & A1 &”?”, and name a cell Greet with the opening message.
- Another useful function is =char(10), which inserts a line break into your text.
I recently received my course evaluations for the Fall 2013 term. They’re linked below.
Dr. Angelo Camillo presented this at Woodbury’s weekly colloquium.
I was able to present some of my summary work on Moocs at the eLearn conference today. Below is my presentation, but my written paper is also available for download. This presentation was a collaboration with a Woodbury MBA Student, Yesica Allaya.
How will Moocs influence the college and university system? This paper examines them with the “iron triangle” principles of access, cost, and quality. These show that while Moocs have lower cost, they also have reduced access. Because they reduce person-to-person contact, students must already be skilled life-long learners. In addition, while Moocs are currently free, this is a result of them being subsidized. Their actual cost per successful completer is closer to 1/10th of a traditional face-to-face or online course. As a result, Moocs are unlikely to significantly affect existing face-to-face or online delivery mechanisms. Instead, they are likely to reach new life-long learners.
Woodbury has a number of PPDV100 courses to help students get off to a good start. They’ve been asking professors to put together some short videos, so I created one on how to manage your school files. The short answer is to use Dropbox, organize files by a separate folder for each class, and use file names to track document revisions.
You can also download a PDF version of these slides by clicking here.