Tutorial blog 2: Excel

Excel was introduced during week 5.  There are so many different things you can do within excel ranging from very easy to difficult.

We learned about the structure, data basics, creating formulas, and functions during the Excel lessons.

If you have a specific question about something on excel you can either use the excel help button in your excel document or click on the following link: Excel Help

Structure:

Columns are rows are labeled with numbers and letters.  Rows are number from 1 to 1,048,576 and columns are labeled with letter from A to XFD.

We were taught that to re-name a worksheet you must double-click on the "sheet" on the left bottom corner of the sheet you wish to re-name

Data Basics:

To record information as text data in a cell you click "single quote" or put an apostrophe before any information.  Excel stores data by using a serial number.  Excel can determine how many days have passed since January 1st, 1900.  A very large number that could take a while to come up with by hand is solved in seconds on my excel.

Adding rows and columns is easy on excel.  You right click on the cell that you want to add the new row to and click "insert row" and all the old data will move down a row.  Adding a column is the exact same concept except you have to right click on the cell that you wish to move to the right.

This is a brief video explaining how excel works and tricks to saving time by using excel instead of doing each mathematical equation one by one.  It explains great excel is with mathematics and how quickly it can adjust.  This video is from http://www.youtube.com/watch?v=XmSp2-Fa4rg and was created by Excellsfun

Formulas:

To begin a formula you must start with an equal sign (=)

The symbols for addition, subtraction, multiplication and division are +, -,  *, and / respectively.

PEMDAS is used to help remember Parentheses, exponetiation, multiplication, division, addition and subtraction

 This is the screen that will come up when you choose to insert a function.  It will explain step by step what to do and what cells to choose to create your function.  This is a picture of the screen on my computer when I was following the steps on how to create a function.

Functions:

The IF function works like a conditional statement.  It checks to see if a function is true and if it is true it returns one value.  If it is false it returns another value.  The syntax for an IF function is: IF(logical_test, [value_if_true], [value_if_false])

so if a student earns an S or U in the class based on their grades the function would be =IF(Total points > 120, if true they receive "S", if false and they don't earn 120 points, they will receive a "U".

Excel Project

           This week in ISM3004 we had an Excel Project due.  We were supposed to help an owner research the effectiveness of a particular workout by analyzing heart rates.   I just completed the project and learned many new things and tricks about excel.  It started off very basic.  Changing the title of the worksheet and changing the font were the first few steps of the project.  As the steps went on it got a little more difficult.  We had to "freeze" the first two rows which makes it a lot easier to analyze the data and see what each number is for without scrolling all the way up to see the column labels. "Freezing" is when the rows you select continue to stay on top of the page so when you scroll down you dont get confused with what each number is representing.

                      http://premiercoachingonline.com/2010/03/heart-rate-training/

            Next we found the Target and Maximum heart rate.  The Maximum Heart rate is found by subtracting the age from 220 and using the same formula for all of the cells in that column.  The Target heart rate was alittle more difficult to find.  We had to make a formula so the target heart rate was 80 percent of he maximum heart rate.

  Next we determined if each person had achieved their target heart rate by looking at their highest heart rate and seeing if it was greater than or less than the target heart rate.  We also determined how many males and females who achieved or did not achieve their target heart rate.   

Attached is a "screen capture" of the "AvgIncrease" Pivot Table I derived from my project.

Finally we determined by how much their highest heart rate increased compared to the orginaly heart rate taken at time zero

Completing this excel project is a fantastic way to get used to and understand the basics as well as some of the more complex projects you can create within excel