(A) Averages and Pivot-Tables
Business students are often sought by employers for their knowledge of Excel and ability to analyze data within Excel efficiently. Often, they are asked to perform rather simple tasks, including the calculation of averages across different categories of data and expressing the results in a bar chart. Most people in an organization know very little about statistics, and if averages and graphs are all they understand, that is all they will ask you to do. The previous article on histograms provided a tutorial for making bar charts. This article shows you how to use pivot-tables within large databases for calculating the averages which can comprise the data in a bar chart.
The best way to understand pivot-tables is to see a video demonstrating how it is constructed and used. The video below takes data on salaries of graduates from Kansas State University. Collected from a survey in 1997, the survey not only contains individuals' salaries but their work experience, gender, location, marital status, and degrees. All have an undergraduate degree in agricultural economics; agronomy; animal science; or grain, feed, milling, and bakery science. Some have Master's degrees.
The video below demonstrates how to quickly answer the following questions. I advise you download the data used in the video and follow along, attempting to do everything the tutorial does. Pivot-tables are something you must practice to truly understand. Below are the types of questions one can quickly answer using pivot-tables.
- What are the average salaries for the four majors?
- Do married people make more than unmarried people?
- Do males make more money than females?
- Are higher salaries given to people in cities, versus rural areas?
Video 1—Tutorial on Pivot-Tables
(B) VLOOKUP Function and Pivot-Tables
Many of my former students remark how often they use the VLOOKUP function in Excel (also in Google spreadsheets) in the workplace, so it's worth discussing here. As with pivot-tables, it is best seen in an example, so the video below shows how to use both the VLOOKUP function and pivot-tables to analyze the shooting ability of AGEC 4213 students. You may follow the video by downloading the data here.
