AEA365 | A Tip-a-Day by and for Evaluators

TAG | excel

Hi! I’m Sarah Dunifon, Research and Evaluation Associate at the Wildlife Conservation Society (WCS). In my role, I create many reports and I’m always looking for efficient tools for data visualization. I’ve found a few different programs to display location data, which I’d like to share with the wider AEA community.

Rad Resources:

Google Fusion Tables

Google Fusion Tables is an experimental app add-on that you can link to Google Drive. It allows users to create online, interactive heat maps and feature maps. Privacy settings are managed in the same way as other Google products where users can decide on a range between public and only available to you as the user. Viewers can manipulate the maps in various ways, such as filtering results, scrolling around the map view, or switching between map types.

Feature map of countries with WCS offices – Fusion Tables

Infogr.am

Infogr.am offers quick and easy interactive heat maps which can be shared via weblink. The free version includes a United States map and a world map, and your data will be public, whereas the paid versions have data privacy, more map choices, and the option to download the maps as images.

Heat map of countries with WCS offices – Infogr.am

Excel Apps – Geographic Heat Map

With the “Geographic Heat Map” app on Microsoft Excel, you can create either a world or United States heat map. The data is private and you can save your final map as a picture, making it a good option for inserting into a report. This app doesn’t have much customizability in color and style, but I’ve been able to paste the image into another program (say Microsoft Word or PowerPoint) and edit the image there.

Heat map of countries with WCS offices – Geographic Heat Map with Excel Apps

Tableau

Tableau offers feature maps and heat maps for free, though the data will be public. This program is highly customizable and makes some beautiful visualizations. However, you might find there is a bit of a learning curve to using this software. The visualizations can be saved as an interactive display in “presentation mode” or uploaded to the Tableau Public gallery where they can be shared digitally.

Heat map of countries with WCS offices – Tableau

Excel Powermap

Powermap in Microsoft Excel lets you create private feature maps in a variety of themes on a 3D globe. The map can be an online interactive display or an image produced by taking a screen grab through the program. The screengrab puts a picture of the image onto clipboard, which you can then paste into another program.

Feature map of countries with WCS offices – Excel Powermap

Hot Tip: Consider how you intend to use the map before you start building it. If it needs to be interactive, choose an online format. If it needs to be put into a report, pick a program with capabilities to export a high-resolution image, rather than just a screenshot.

Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org . aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators.

 

·

Greetings Colleagues!  We are Becca Sanders (Hood River, Oregon) and Heather Robinson (Rochester, New York) of Iteration Evaluation LLC. Our forte is the “…ahhh… there is no button on your system for that” analysis requests.  We work mostly with behavioral health organizations whose current informational needs often far exceed what existing systems can pull off.

Enter VLOOKUP.  VLOOKUP is a tool in Excel that can be used to amalgamate data data that lives on seperate spreadsheets.  It’s like a 4 wheel drive vehicle that can get stakeholders what they need across the potholes that result from siloed management information systems. For example, say we have a direct service provider with a caseload of folks he or she is trying to help.  We also have spreadsheet X, that lives in one place, and gives the provider a list of something important—say emergency room visits.  And we have spreadsheet Y that lives elsewhere and contains some other important piece of information about individuals the provider is trying to help—say existence of a chronic condition.  The provider wants this information all in one place across a particular group of individuals—say a caseload– in order to do their job well.  Enter VLOOKUP as the solution; a solution while you wait for relational table builds, system integration, or the like.  One common identifier across different spreadsheets (for example, a person’s ID number) PLUS a bazillion exported spreadsheets, none of which quite do the trick, PLUS VLOOKUP is all you need.  VLOOKUP essentially says “hey, here is an ID number for an individual in this table and that one (and that one and that one ad nauseum).  Let’s use it to pull in all values from one spreadsheet to the other (and so on) until all the information you need is in one place!” In that way, VLOOKUP allows you to deal with situations where the snapshot you are trying to take involves variables that live in different systems – especially systems that don’t speak to one another very well.  Get good at it! It will take you far!  It is like a Subaru in the winter which can get you through just about any wacky data set up you are faced with.

Rad Resource: Need more of a leg up to get started? Check out the free resource www.chandoo.org!  Chandoo has a free blog, with a mission to help anyone become awesome in EXCEL.

Go VLOOKUP! Don’t wait to kick your car into VLOOKUP 4 Wheel Drive!

Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org . aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators.

Hello!  I am Jay Szkola, senior program analyst in the Strategy, Evaluation and Learning Division at Good Shepherd Services. As an internal evaluator, I have the opportunity not only to evaluate the programs in my portfolio, but also assist staff in using data in their day-to-day work with participants. This doesn’t have to involve a great deal of complexity, and can be as simple as developing tools that translate data into an accessible format.

Welcome to internal evaluation week, hosted by the Internal Evaluation TIG! Thanks to all of the internal evaluators who contributed to this week’s blogs and to Annie Gleason (Good Shepherd Services in New York City) for coordinating the work! This week is dedicated to sharing insights and tools from evaluators across the country (and Canada!) about how they help organizations to put data and evaluation findings into action. Blogs this week offer a broad range of suggestions for helping stakeholders to use data, drawing on technology, data visualization, communication and framing, and participatory methods.

All of the community-based youth justice programs at our agency use the Positive Youth Development Inventory as a tool to help evaluate our impact on key youth development constructs, such as friendship and future orientation. We use the PYDI to assess our participants at baseline, and gauge pre-to-post changes upon program completion. These insights are useful for program planning and advocacy.

I also wanted to make sure staff could use the results to inform their work with participants directly, but initially experienced limited success with this goal. One barrier was that while staff liked the survey, they were unsure how to translate individual survey responses into insights and action.

Cool Trick:

In response, I constructed a simple Excel sheet to help with this translation.  Program staff enter in participant survey responses, and get out easy-to-read graphs showing how the participant scored in each construct. As staff enter the data, a table aggregates each response by construct, which then links to the graphs.

Program staff and leadership liked the tool so much that when the NYC Department of Probation adopted the PYDI as a measure, the Excel tool was shared and is now used by other youth justice programs in the city.

(click for larger images)

PYDI Template Input

PYDI Template Input

PYDI Template Output

PYDI Template Output

Lessons Learned:

Offer a guide. For those who do not have a high comfort level with data, colorful graphs alone will not bring about understanding. Included on the Excel tool is a brief, jargon-free definition, a sample question, and a list of questions for each construct on the survey. 

Think about multiple uses to get maximum impact. Whatever the data source (surveys, administrative data, etc.), talk to staff about how they could use the data. Once staff in our youth justice programs began seeing the PYDI as something they could use in day-to-day practice, it became less of a task to be completed “for the evaluators” and instead, a shared project.

The American Evaluation Association is celebrating Internal Evaluation (IE) Topical Interest Group Week. The contributions all this week to aea365 come from our IE TIG members. Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org. aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators.

Hi! I’m Laura Sefton, Project Analyst in the University of Massachusetts Medical School’s Center for Health Policy and Research. Many AEA365 readers use sophisticated statistical software like SPSS, SAS, or Stata for their data analytic needs, but others don’t have ready access to such software and depend on more freely available options like Microsoft Excel. Excel is built to do many computational functions, but it can take time to research its help pages to get it to do what you want. I found a few functions helpful for summarizing both numeric and text-based data that my colleagues and I collected during a recent set of over 30 interviews with Medicaid members. Using the sample data below, I’d like to share what I’ve learned with you.

sefton-1

Computing means, modes, and medians: To calculate the mean of the number of ER (Emergency Room) visits, the formula instructs Excel to ‘average’ data in a range of cells. Using the sample data above, the formula below computes a value of 4.71 for the data in column A. To compute the median or mode, substitute ‘median’ or ‘mode’ for ‘average’.

=AVERAGE(A2:A8)

Counting text data: Excel can also count up the frequency of text responses. The formula is structured much like computing a mean, with the instruction to ‘count if’ certain criteria are met in a set of cells. The formula below asks Excel to count how many times the word ‘Physician’ appears in rows 2 through 8 in column C. Note the use of double quotes around the criteria. You will have to create a calculation for each separate criteria, but you can easily copy/paste and update the formula.

=COUNTIF(C2:C8, “Physician”)

Counting data grouped in ranges: We often report data in ranges and Excel can easily do this using the ‘countif’ function. Your formula needs to account for 2 criteria: the minimum and maximum numbers in the range. Note that you’ll need to add an ‘S’ to the ‘countif’ command since there are since 2 ranges are being compared.

=COUNTIFS(B2:B8,”>=19″,B2:B8,”<=25″)

Hot Tip: Create, format, and populate your summary data display in Excel. The formulas will work behind the scenes to make updates if you make any changes to your source data.

sefton-2

Hot Tip: Right click on status bar at the bottom of your Excel page and select calculations that you’d like to see on the bar. Excel will automatically compute and display the average, minimum, maximum, count and sum, among other options, for a set of highlighted cells.

Rad Resource: Beyond searching the Web using keywords, a good resource is Microsoft Office Support, which provides an Excel Help page, a descriptive list of Excel’s statistical functions and some tutorials.

Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org . aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators.

·

Hello! I’m Tony Fujs, Data Scientist at the World Bank. As many fellow data nerds, I love to visualize the data I have in my hands, but I also like to spend time visualizing data that I don’t have: Missing data.

Why would I do that? Because bad handling of missing data can seriously bias the results of a statistical analysis.

Hot Tip:

  • Removing missing records from a dataset is generally not the right way to handle missing data!
  • For more information about missing data, and techniques to address it, see:

Baraldi, Amanda N., and Craig K. Enders. “An introduction to modern missing data analyses.” Journal of School Psychology 48.1 (2010): 5-37.

In this post, I will present a common heatmap variation technique used to visualize missing data, and identify potentially harmful missingness patterns.

STEP 1: The data table

Here is a dataset containing fake education records. In this dataset, each row represents a unique student, and contains information about the following variables:

  • Gender
  • IQ scores
  • Reading grades
  • Math grades

fujs_image1

It’s relatively easy to spot missing data in this table, but I want to make the missing cells impossible to ignore. Let’s color each missing data cell in a bright, easy to spot color!

STEP 2: Choose a catchy color to highlight empty cells

Now, I really can’t ignore these empty cells, but I still can’t see – at least not easily – if there is any interesting pattern in the missing data.

fujs_image2

Next, I will use some heat map technique to colorize the non-missing cells of this table.

STEP 3: Color the non-empty cells according to their values

I will use a monochrome color scale. Since I am interested in comparing empty cells with non-empty cells, I want to keep the color scheme as simple as possible to facilitate this comparison. The shades of grey highlight the change in values of the non-empty cells, while maintaining a strong contrast with the red empty cells.

Low values are colored in light grey, while high values are colored in dark grey. Since we are only interested in checking missing data patterns, the actual values of the non-empty cells can be hidden.fujs_image3

STEP 4: Remove the values

Some pattern now seems to emerge. But it is stillnot as obvious as it could be…

fujs_image4

 

Let’s reorder the rows according to the values of the IQ scores columns.

STEP 4: Sort columns values

It is now hard to miss the pattern of the missing data. Reading grades are missing for the lower half of the IQ score distribution… Assuming that a correlation exists between IQ scores and reading grades, removing the empty cells from the table would overestimate the average reading grade for this group of students.

fujs_image5

 

Rad Resources:

  • This visualization can be easily produced using the R package VIM:

http://www.statistik.tuwien.ac.at/forschung/CS/CS-2008-1complete.pdf

  • It can also be done in Excel using a couple of hacks:

http://policyviz.com/create-a-heatmap-in-excel/

 

The American Evaluation Association is celebrating Data Visualization and Reporting (DVR) Week with our colleagues in the DVR Topical Interest Group. The contributions all this week to aea365 come from DVR TIG members. Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org. aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators.

·

Hi, we are Pei-Pei Lei and Carla Hillerns from the Office of Survey Research at the University of Massachusetts Medical School’s Center of Healthcare Policy and Research. The other day, we asked each other what one analysis tool is most vital to our quantitative survey work. We agreed on the answer – a banner table.

A banner table is a simple thing, really – just a set of crosstabs – but it’s so useful in analysis. For example, the table below shows how people of different ages and insurance types differ in their experiences with their doctors. By displaying all our key variables in one view, a banner table helps us to visualize stories from the data. It allows us to understand if subgroups of our respondents have different behaviors/opinions without having to run multiple analyses.

Lei Hillerns

Hot Tip: We’ve used age and insurance type as our banner points in the table above. Both were collected as part of the same survey that asked respondents how often their doctors listened carefully to them. However, you can use multiple sources to create banner points, such as background data on the sample or previous waves of the survey.

Hot Tip: In setting up your tables, incorporate statistical test results so you can communicate statistically significant differences easily. In the above table, superscripts indicate statistically significant differences between banner points at the 95% confidence level.

Hot Tip: There are plenty of crosstab software packages that can create a large number of banner tables easily, but they usually come with a fee. If you have a limited budget or a small dataset, consider creating your banner tables through tools you already have. Here are a few software packages you might have and how you can create your banner tables with them:

  • Excel: pivot table
  • R: table function
  • SAS: proc tabulate function
  • SPSS: crosstabs comment
  • STATA: table comment

Rad Resources:

  • Want more information on banner tables? Check out these websites for more details and examples:

http://www.greenbook.org/marketing-research/anatomy-of-a-crosstab-03377

http://www.statpac.net/crosstabs-software.htm

  • Are you a Qualtrics user? Here’s a helpful guide to creating crosstabs using your survey software:

https://www.qualtrics.com/support/survey-platform/data-and-analysis-module/cross-tabulation/cross-tabulation-overview/

  • Are you a Confirmit user? Confirmit has a built-in tool, Instant Analytics, for creating banner tables:

http://betatesterconfirmitcommunity.ning.com/discussions/instant-analytics-now-available-to-all-confirmit-professional-use?context=category-Instant+Analytics

Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org . aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators.

 

Hello, friends! My name is Yuqi Wang, and I work at Innovation Network. I love figuring out different ways to visualize data, and I want to show you how simple it is to create one of my favorite graphs in Excel—what I like to call the “filled-in bar chart.”

wang 1

  1. Set up your data

List the two time frames in Column A in excel, and label the column “Time frame.” In Column B, enter the two corresponding data points, and label it “Data.”

Hot Tip: This graph works best with percentages, so other than time frames, make sure the rest of your data points are in percentages.

Next: to give our chart the filled-in effect, label Column C “Bottom shading,” and enter the same numbers from Column B in the same order. Then label Column D “Top shading,” and enter the percentage you get when you subtract each of the numbers in Column C from 100%.

Your spreadsheet will look like this:

Wang 2

2. Visualize your data

Highlight the eight data points, and insert a chart. It doesn’t matter which one because we’ll be creating a combination chart. This step is to create a base for our finished graph.

Right click on the chart, and click “Select Data.” Remove all the series under “Legend Entries” because we’re going to enter our own data sets. The prepopulated series will not create the type of graph we want.

Wang 3

Under “Legend Entries,” click on “Add.”

Wang 4

In “Series name”, select the cell that contains the word “Data,” and for “Series values” select the data set under “Data.” Click “Ok.”

 

Wang 5

Next, find the “Horizontal (Category) Axis Labels” section, and click on “Edit.”

Wang 6

In the “Axis label range” box, highlight the two timeframes, and click “Ok.”

Wang 7

Repeat the entire process for “Bottom shading” and “Top shading.”

 3. Reformat your data

After you have your chart, right click on your graph, and select “Change Chart Type”. Under the list of charts, select “Combo,” and within that, choose “Custom Combination.”

“Bottom shading,” “Data,” and “Top shading” data will be displayed, along with the ability to change each data set’s chart.

Choose “100% Stacked Area” chart for “Bottom shading” and “Top shading.” Then choose the “Line with Markers” chart for your “Data.” Click “OK.”

Wang 8

You’re almost done! Delete the numbers you don’t want, delete the legend, and play with the chart size and colors!

Hot Tip: You can create a small multiples chart by copying and pasting multiple filled-in bar charts together!

Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org . aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators.

My name is Lisa R. Holliday, and I am a Data Architect with The Evaluation Group. Cleaning data is a large part of my job.  For studies that need to match participants’ names or IDs, it is critical to ensure that information is entered consistently from one data collection cycle to the next. However, depending on the data collection tools available, it’s not always possible to do this. Cleaning and matching data can be time-consuming.

I’ve found two useful tools that have helped to reduce the amount of time I spend working with these data and increased matching accuracy.

Rad Resource 1: FuzzyMatch Macro for Excel

FuzzyMatch is a free Excel macro available from the Mr.Excel message board. It creates three functions:

  1. FuzzyVlookup
  2. FuzzyHLookup
  3. FuzzyPercent

FuzzyVlookup and FuzzyHlookup look up values against a master list and suggest possible matches. FuzzyPercent allows you to assess the similarities between the proposed matches and the master list. In my testing, FuzzyMatch was able to correctly match values that differed by as many as nineteen characters in length.

One drawback to this macro is that it can be time-consuming to run. During testing, it took Excel approximately one minute to process 942 rows using FuzzyVlookup and FuzzyPercent concurrently. Additionally, the functions have volatile characteristics (they sometimes recalculate when you save the workbook). To avoid this, after running the functions, copy and paste the results as text.

This video demonstrates how to use FuzzyMatch.

Rad Resource 2: iugum Data Matching Software

This software performs much like the Excel macro, except it is faster and more user-friendly. There is a 14-day free trial available, and subscriptions run from $99 to $400. During testing, it matched over 8,000 rows of data in less than one minute. If unable to perform an exact match, iugum provides possible matches, and it is easy to accept or reject suggested matches. If you regularly clean data using a master list, this is a valuable tool.

This video provides an overview of iugum Data Matching Software.

We’re celebrating 2-for-1 Week here at aea365. With tremendous interest in the blog lately, we’ve had many authors eager to share their evaluation wisdom, so for one special week, readers will be treated to two blog posts per day! Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org . aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators.

 

 

· ·

My name is Lisa R. Holliday, and I am an Evaluation Associate with The Evaluation Group in Columbia, SC. I was recently cleaning survey data and preparing it to be uploaded into a database. The survey had been offered multiple times, and I wanted to track the responses of participants who had completed the survey each time it was offered. To start with, I needed to create a list of non-duplicated names. I would then be able to use this list to determine which participants had taken the survey each time it was offered.

Hot Tip: Power Query can remove duplicates from a list. Power Query is a handy add-in for Microsoft Excel that allowed me to generate my list quickly and easily. Power Query is a business intelligence tool that works with Excel 2010 and 2013. It is available for free download at the Microsoft website: http://www.microsoft.com/en-us/download/details.aspx?id=39379

Cool Tricks:

Step 1: I created a unique field in my survey data that would allow me to identify each person.  Just in case I had two people from the same site with the same name, I concatenated their name with their location and job title. The final cell looked like this:

Holliday 1

I then created a master list of names (with duplicates) using cut and paste. Once this was done, I was ready to load my data in to Power Query.

Step 2: I selected to load my data “From Table” under the Power Query tab.

Holliday 2

Step 3: Within the Power Query window, I selected the concatenated column, and then “Remove Duplicates.”

Holliday 3

After my query ran, I selected “Close and Load.” Excel created a new table comprised of unique values only.

Holliday 4

Hot Tip: Why not use “Remove Duplicates” from the Data Tab?

Any data you load into Power Query can be refreshed, and the query will automatically be re-executed. This feature is valuable if you plan to add more data to your original data set. In contrast, “Remove Duplicates” under the Data Tab does not have this option.

Hot Tip: Other Functionality. Power Query has a lot of other useful functionality and is worth exploring. It can easily import data from a variety of sources (including websites), un-pivot data, and split columns (such as First and Last name).

Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org . aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators.

·

Hi, I’m Harlan Luxenberg from Professional Data Analysts, Inc., a public he alth evaluation firm in Minneapolis, and I’d like to share some thoughts about certain situations where databases may be more useful than Microsoft Excel.  Excel is great for quickly crunching data and managing small datasets; however, using Excel in the wrong situations can actually make your data management tasks trickier.

Below are problems that our colleagues have encountered in Excel and reasons why we think that databases would be better solutions in these cases.

Hot Tip: Know which situations to use a database.

Luxenberg

Rad Resource: Anyone can learn databases!

While the thought of learning databases can sound intimidating, anyone can learn them and there are tons of resources that will help you get going! There are numerous blogs and websites, and even free online classes such as Coursera.

Rad Resource: Start with Microsoft Access

A good database to start with is Microsoft Access (http://office.microsoft.com/en-us/access/), which is part of the Microsoft Office Professional suite, and which may already be installed on your computer. Microsoft Access allows users to build reports, create data collection forms, visually create tables, and integrates seamlessly with Excel (which you can still use to create beautiful charts).

Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org . aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators.

·

Older posts >>

Archives

To top