Showing posts with label excel. Show all posts
Showing posts with label excel. Show all posts

Friday, October 1, 2010

Excel Project

Excel - applied
In week 5 and 6 of the course, I also had to work on an Excel Project. The story of the task dealt with a large fitness center, whose owner collected data from 500 subjects. I was provided with an Excel spreadsheet, showing age, gender and 21 heart rates measured for the subjects during a workout.

First of all, I had to make some simple enhancements to the sheet and prepare it to be printable. I calculated the maximum heart rate for each subject by adding a new column and using the formula Maximum heart rate = 220 - Age. Furthermore I calculated the target heart rate for each respondent, which is 80% of the maximum rate and then investigated if the maximum heart rate achieved during the workout was below or above this target rate. In the end of the first step I calculated the percentage increase of the different heart rates during the workout. For each of the calculations, new columns were added and absolute as well as relative cell addressing was used to make minor changes to the data more convenient.

In the second step of the project the data was analyzed using Pivot Tables. The first question to answer was, how effective the exercise regimen was, more specifically: “how many subjects reached their target heart rate during the 15 minute exercise window?” By using Excel's Pivot Table analysis tool this was quite easy to find out. I first grouped the subjects into males and females by dragging gender into the row labels, then dragged TgtAchieved into the column labels to answer the question. As I was interested in the number of people having achieved the target, I lastly changed the values from "Sum" to "Count".
In the second question, the owner was interested in the average percent increase, grouped by gender and age groups, to find out more about demographic differences. Therefore again a Pivot table was used, which can be seen below:


To create this table I used subject age for the rows (grouped by decades) and put gender in the columns. As once again I was not interested in the sum but in the average percentage increase I dragged "PctIncreaseHR" in the value area and selected "Average".

By this project I was able to apply knowledge acquired in week 5 to a "real world problem". This deepened my understanding of certain Excel functions and tools and added some valuable knowledge for working on further Excel spreadsheets. Especially the Pivot Table function turned out to be quite useful in analyzing data, which might be very beneficial for me the future.

Sunday, September 26, 2010

Week 5: Clear

This week Microsoft Excel was introduced. We learned how Excel works, for what it can be useful and some of the numerous functions were illustrated. Although I have already worked with Excel during my study there were some functions explained which I have not known before. One of these was the function "right". With the help of this function you can tell Excel to copy parts of a cell to another cell. If e.g. one cell contains a first and a last name and you want work further only with the last name in another cell, you can use the function "right", indicate how many characters of the original cell you want to rewrite in the other cell, and, thus you can seperate first and last name from each other. The same also works with "left", this time only the left part of a cell is used. For indicating how many characters you want to copy, there is a further function called "find" which helps you identify the number of characters written until a certain symbol, like e.g. a comma.

I think this function is rather important, because in today's business environment it is all about digital information, which is compiled by one person and edited by another worker. Often the second person needs to focus on a different aspect of a particular document and thus needs to seperate data which may be written in one cell. Thus, with the help of these functions, you can align different data formats to let operations run smoothly.