ME1720 Software Advanced Excel, Lesson 1 – Advanced Functions
Document URL: http://ide20.com/upload/ModuleAE/Lesson01_JBS.pdf
Developer: [email protected], 8/2015 update by [email protected]
Copyright 2010, Missouri S&T
1
Advanced Excel
Lesson 1 – Advanced Functions
Pre-reqs/Technical Skills
• Office for Engineers Module
• Basic computer use
Expectations
• Read lesson material
• Implement steps in software while reading through lesson material
• Complete quiz on Blackboard
• Submit completed assignment on Blackboard
• Ask questions as necessary
Objectives/Measurables
• Know the different parts of a function, and how to correctly enter arguments, measured via Blackboard quiz score
• Learn about several often-used Excel functions, measured via Blackboard quiz score
• Learn how to convert equations and process that you are familiar with into a format that Excel understands, measured via the score on the assignment
Table of Contents
Advanced Excel …………………………………………………………………………………………………………………… 1
Lesson 1 – Advanced Functions ………………………………………………………………………………………………………………………………………. 1
Pre-reqs/Technical Skills ……………………………………………………………………………………………………………………………………………………….. 1
Expectations ………………………………………………………………………………………………………………………………………………………………………………. 1
Objectives/Measurables …………………………………………………………………………………………………………………………………………………………. 1
1. Introduction ……………………………………………………………………………………………………………………………………………………………………. 2
1.1 The Insert Function Window ………………………………………………………………………………………………………………………………………….. 2
2. Parts of a Function …………………………………………………………………………………………………………………………………………………………. 3
3. Data Management Functions ……………………………………………………………………………………………………………………………………….. 4
3.1 The COUNT Function ………………………………………………………………………………………………………………………………………………………… 4
3.2 The COUNTA Function ……………………………………………………………………………………………………………………………………………………… 5
3.3 The COUNTIF Function …………………………………………………………………………………………………………………………………………………….. 5
3.4 The SUMIF Function …………………………………………………………………………………………………………………………………………………………. 6
3.5 The IF Function ………………………………………………………………………………………………………………………………………………………………….. 6
4. Mathematical Functions ………………………………………………………………………………………………………………………………………………… 9
4.1 The RADIANS Function ………………………………………………………………………………………………………………………………………………….. 10
4.2 The SIN and COS Functions ………………………………………………………………………………………………………………………………………….. 10
4.3 The SQRT and POWER Functions ……………………………………………………………………………………………………………………….. 10
5. Conclusion …………………………………………………………………………………………………………………………………………………………………….. 11
Assignment ………………………………………………………………………………………………………………………. 12
ME1720 Software Advanced Excel, Lesson 1 – Advanced Functions
Document URL: http://ide20.com/upload/ModuleAE/Lesson01_JBS.pdf
Developer: [email protected], 8/2015 update by [email protected]
Copyright 2010, Missouri S&T
2
1. Introduction
In the Office for Engineers Module you learned about a few basic functions, including Sum and Average, these functions are just a couple of the hundreds of Excel functions available. This huge library of functions can be extremely powerful, if you know how to use it. In this lesson you will create two different spreadsheets using functions, a class grade sheet and a projectile motion spreadsheet.
1.1 The Insert Function Window
There are many, many functions in Excel. Let’s look at a list of all the available functions.
1. Go to the “Formulas” tab on the Ribbon
2. Click the “Insert Function” button on the far left of the ribbon
3. In the insert function window that opens, functions are listed in the lower box; Use the drop down box to filter and view functions by category
4. Look through and read the descriptions of a few functions
Figure 1 – The Insert Function Window
• Refer to the Insert Function Window if there is a problem that could possibly be solved with functions, but you don’t know the name of the function
o The search feature in this box is invaluable for finding new functions. It works best if the main component of what needs to be done is searched for. Example: If looking for the function to add all the cells in a column, you would search for “Add”
ME1720 Software Advanced Excel, Lesson 1 – Advanced Functions
Document URL: http://ide20.com/upload/ModuleAE/Lesson01_JBS.pdf
Developer: [email protected], 8/2015 update by [email protected]
Copyright 2010, Missouri S&T
3
• While the insert function dialogue box can be used to insert functions into the spreadsheet, it does not need to be used every time a function is used.
o Recall: to use a function on a spreadsheet type “=” followed by the function name
• If you need more information about a function or extra help with getting a function to work, the blue hyperlink in the bottom left corner of the insert function dialogue box will open another window. This window has additional information about the use of the function and an example of the function in use.
2. Parts of a Function
There is a specific format that all functions are displayed throughout Excel, and if the parts of a function are understood then using them will be much easier. Here are a few examples followed by an explanation:
Figure 2 – Example of Functions
1. The function name in all caps is first, this is what is typed to call (use) the function.
o When typing in a function, you don’t need to type it in all caps, the program will automatically change it to caps when you are finished
2. Following the function name is an open parenthesis, followed by a list of arguments.
o A function can have any number of arguments including zero. Arguments are the input needed by the function in order to output a value.
3. If there is more than one argument, a comma is used to separate them.
4. After all of the arguments, there is a close parenthesis. Excel automatically inserts this and it is important because it tells both the user and the computer that they have reached the end of the function
o This may seem trivial, but is an extremely useful tool to keep track of what level you are on when working with nested (one function inside another) functions
Figure 3 – Excel’s Dynamic Input
ME1720 Software Advanced Excel, Lesson 1 – Advanced Functions
Document URL: http://ide20.com/upload/ModuleAE/Lesson01_JBS.pdf
Developer: [email protected], 8/2015 update by [email protected]
Copyright 2010, Missouri S&T
4
• When manually using functions in workbooks, Excel will help with a dynamic input that displays the function in standard format, reminding the user of the number of arguments and order of input
• From here on out, anytime a function is mentioned in this module, it will be stated in all caps like it is in Excel, for example: “AVERAGE is a very handy tool.”
3. Data Management Functions
Excel is outstanding at managing and interpreting large amounts of data, and has many functions available that are useful for just this purpose. To give you a brief overview of some of the data management functions available, you will be constructing a mock grade sheet including functions to evaluate statistics and letter grades.
• [Download the “Class Spreadsheet” from the website], when opened should look like the picture below
Figure 4 – Class Spreadsheet
3.1 The COUNT Function
You are going to use the COUNT function to find out how many students are in the class. The argument, (the required input), for COUNT is a range a cells, COUNT then returns an integer representing how many cells with numbers are found in the range.
Figure 5 – The COUNT Function
1. Select cell H2
ME1720 Software Advanced Excel, Lesson 1 – Advanced Functions
Document URL: http://ide20.com/upload/ModuleAE/Lesson01_JBS.pdf
Developer: [email protected], 8/2015 update by [email protected]
Copyright 2010, Missouri S&T
5
2. Enter “=COUNT(A:A)”, the argument “A:A” tells the COUNT function to check every cell in the A column for numbers.
o The argument “A:A” can also be entered by clicking on the “A” title on the grid axis label when inside the parenthesis of the function
3. It is always a good idea to label what numbers are in spreadsheet so it is easy for other people to use. So in G2 enter “Total Students:”
Figure 6 – Total Students
3.2 The COUNTA Function
You are going to use the COUNTA function to count how many students are present in class. COUNTA is very similar to COUNT, but instead of numbers it checks for any value, including letters.
Figure 7 – The COUNTA Function
1. Select cell H3
2. Enter “=COUNTA(B:B)-1”, again this tells COUNTA to check all the cells in column B for any value. The “-1” subtracts one because COUNTA will count any filled cell, including the column title and any adjustments like this must be made manually
3. In G3 enter “Students Present:”
Figure 8 – Students Present
3.3 The COUNTIF Function
You are going to use the COUNTIF function to count how many students answered true or false. This is similar to the previous two functions, but works by checking the designated cells for the user defined criteria, (which can be having certain letters, numbers, or fulfilling mathematic inequality or other test), and only counts the cells that fulfill the criteria.
Figure 9 – The COUNTIF Function
1. Select cells H4
2. Enter “=COUNTIF(C:C,”TRUE”)”, this is similar to the last two functions, but it has an extra argument. The second argument is the value that you want to count and needs to be in quotations; in this case, COUNTIF checks each cell in the C column for the word “TRUE”.
3. In G4 enter “True:”
ME1720 Software Advanced Excel, Lesson 1 – Advanced Functions
Document URL: http://ide20.com/upload/ModuleAE/Lesson01_JBS.pdf
Developer: [email protected], 8/2015 update by [email protected]
Copyright 2010, Missouri S&T
6
4. Select H5 and repeat the procedure using column D:D and “FALSE”, then enter “False:” in G5
Figure 10 – True and False Answers
3.4 The SUMIF Function
Let’s say you need to sum the “Question 2” column of the students, but only the ones who answered “true” for question 1, the SUMIF function is needed. SUMIF checks in a designated column for a user defined criteria, like COUNTIF, and then can sum the cells that match the criteria or corresponding cells in a different column.
Figure 11 – The SUMIF Function
1. Select H6
2. Enter “=SUMIF(C:C,”TRUE”,D:D)”, the first argument is the range which SUMIF checks for the criteria, the second argument is the criteria SUMIF tests for, and the third argument are the cells that SUMIF sums if their corresponding cell fulfills the tested criteria.
o The 2 columns of cells are related by the order they are listed, to put it another way the first cell of the first column corresponds to the first cell of the second column, the second cell to the second cell and so on.
3. In G6 enter “Sum of True’s:”
Figure 12 – Sum of True’s
3.5 The IF Function
There is a list of grades percentages in the spreadsheet, but not the letter grades. For a smaller class it may not be too hard to go through and manually mark each grade, but this class has too many students to easily do this, this is one place where excel functions and a little programming really come in handy. The IF function acts like a simple switch, it evaluates a true of false statement; if it is true, IF does something; if it is false, IF does something else.
ME1720 Software Advanced Excel, Lesson 1 – Advanced Functions
Document URL: http://ide20.com/upload/ModuleAE/Lesson01_JBS.pdf
Developer: [email protected], 8/2015 update by [email protected]
Copyright 2010, Missouri S&T
7
Figure 13 – The IF Function
1. Select cell F1 and enter “Letter Grade”
2. Select cell F2 and input “=IF(E2<60,”F”,IF(E2<70,”D”,IF(E2<80,”C”,IF(E2<90,”B”,”A”))))”, then drag it down all the way to cover all the students. This is actually 4 functions nested inside each other, and it looks more complicated than it really is. Although it is explained, don’t worry too much if you are having a hard time completely understanding it, as it will come up again in other classes, what is important is knowing that functions can be nested inside other functions.
3. The first argument is the logical test, in this case it is an inequality involving the grades, “E2<60” tells the program to check if the value in cell E2 is less than 60.
True: If E2 is less than 60 the function outputs the second argument, which in this case is “F”,
False: If E2 is 60 or greater the function outputs the third argument, which in this case is another IF statement. Inside the second IF, the first argument checks for greater or less than 70, which is the next step up grade wise
True: If E2 is less than 70 the function outputs the second argument, which in this case is “D”,
False: If E2 is 70 or greater the function outputs the third argument, which is another IF statement, this one checking for greater or less than 80, this pattern keeps going until all the possible grades are covered
• This flowchart might help in understanding the nested functions, starting in the leftmost box and moving right, going up if the box you are currently at is true and moving down if it is false, try it by hand with a few different grades to test your understanding.
ME1720 Software Advanced Excel, Lesson 1 – Advanced Functions
Document URL: http://ide20.com/upload/ModuleAE/Lesson01_JBS.pdf
Developer: [email protected], 8/2015 update by [email protected]
Copyright 2010, Missouri S&T
8
Figure 14 – The IF Function Flowchart
• This is the completed Class Spreadsheet, with the 5 statistics over to the right, and the correct letter grades for all the students next to their grades.
• Make sure you save this spreadsheet in a safe place, as you will need it in the upcoming lessons.
Figure 15 – Completed Class Spreadsheet
4th Level
3rd Level
2nd Level
1st Level
Starting Statement
E2 < 60
True:
“F”
False:
E2 <70
True:
“D”
False:
E2 < 80
True:
“C”
False:
E2 < 90
True:
“B”
False:
“A”
ME1720 Software Advanced Excel, Lesson 1 – Advanced Functions
Document URL: http://ide20.com/upload/ModuleAE/Lesson01_JBS.pdf
Developer: [email protected], 8/2015 update by [email protected]
Copyright 2010, Missouri S&T
9
4. Mathematical Functions
Excel is also useful for math applications that require repeating the same calculation many times, or for a large number of problems that can be solved by the same general solution. It is also an excellent tool for exploring mathematical trends. The final part of Lesson 1 of this tutorial will be setting up a spreadsheet to solve the classical physics problem of projectile motion. If you don’t think you have enough of a physics background to calculate the solution, don’t worry, the math is already worked out, you just need to enter the functions into Excel.
• [Download the “Projectile Motion” spreadsheet from the website.] It should be similar to below.
Figure 16 – Projectile Motion Spreadsheet
This spreadsheet is already set and formatted for you, and is divided into three sections:
1. The Input Box: This is where the characteristics of the problem are entered; in this case there are 3 characteristics. These are the only numbers you enter manually.
a. Angle of Launch: angle which the projectile is fired from
b. Launch Velocity: speed at which the projectile is fired
c. Cliff Height: For problems in which cannons are fired off a cliff, it is the height of the cliff, for flat ground problems enter zero
2. The Calculation Values Box: This box is where values that are needed for the calculation are located, but are not wanted answers
a. Angle in Radians: This is the angle of launch in radians (not degrees), which is needed for excel to use trig functions
b. Initial X-Velocity is the x component of the launch velocity
c. Initial Y-Velocity is the y component of the launch velocity
d. Time at Highest Point is the time it takes for the projectile to reach the highest point
3. The Output Box: This box is where the wanted answers are displayed
a. Flight time is the total time the projectile is in the air
b. Total distance is how far the projectile traveled
c. Highest point is the highest altitude the projectile reaches
ME1720 Software Advanced Excel, Lesson 1 – Advanced Functions
Document URL: http://ide20.com/upload/ModuleAE/Lesson01_JBS.pdf
Developer: [email protected], 8/2015 update by [email protected]
Copyright 2010, Missouri S&T
10
4.1 The RADIANS Function
First, you are going to convert the angle in degrees to an angle in radians so that the Excel trig functions can use it. The function is actually called RADIANS, and simply converts from degrees to radians.
Figure 17 – The RADIANS Function
1. Select cell F2
2. Then use RADIANS to convert the angle that will be in B2 to radians
4.2 The SIN and COS Functions
Next we are going to use the SIN and COS functions to get the components of initial velocity, they both evaluate the respective mathematic operations of cosine and sine.
o They will only take angles in Radians.
Figure 18 – The COS Function
Figure 19 – The SIN Function
1. Select cell F3
2. Multiply the COS of the angle in radians in F2 with the Launch velocity in B3
3. Select cell F4
4. Multiply the SIN of the angle in radians in cell F2 with the Launch velocity in B3
• The time at highest point is a simple mathematical relationship involving gravitational acceleration
1. Select cell F5
2. Divide the Initial Y-Velocity in F4 by 9.8
4.3 The SQRT and POWER Functions
Flight Time uses the most complicated math on the sheet, and is a modified form of the quadratic equation:
Flight Time=(-????-v????2-4????????)2????
• Try entering this equation into excel:
1. With b as the Initial Y-Velocity, cell F4
2. With c as the cliff height, cell B4
3. With a as -½ of the acceleration due to gravity, on earth “-4.9”
4. Use the function SQRT with the POWER function nested inside
ME1720 Software Advanced Excel, Lesson 1 – Advanced Functions
Document URL: http://ide20.com/upload/ModuleAE/Lesson01_JBS.pdf
Developer: [email protected], 8/2015 update by [email protected]
Copyright 2010, Missouri S&T
11
o Use as many parenthesis as necessary to group the correct operations together
Figure 20 – The SQRT Function
Figure 21 – The POWER Function
• The Total Distance is another simple mathematic relationship:
1. It is simply the initial X-velocity, cell F3, multiplied by the Flight Time, cell B7
• Finally for the highest point reached, the equation of motion in the Y-direction is needed:
=-4.9????2+?????? +????
1. Enter this equation into excel using:
2. The Initial Y-Velocity, cell F4, for b
3. Cliff Height, cell B4, for c
4. Time at Highest point, cell F5, for t
• The spreadsheet is now complete, enter the following 2 sets of numbers and check it. After you have verified that your spreadsheet is correct, save it in a safe place, because you’ll use it for part of lesson 3.
Figure 22 – Situation 1
Figure 23 – Situation 2
5. Conclusion
As you can see, Excel functions are extremely powerful tools, the spreadsheets you created today are just two very basic ways functions can be used to solve problems. When using functions, remember to be creative, being able to use different combination of functions in conjunction with each other can solve extremely sophisticated problems.
ME1720 Software Advanced Excel, Lesson 1 – Advanced Functions
Document URL: http://ide20.com/upload/ModuleAE/Lesson01_JBS.pdf
Developer: [email protected], 8/2015 update by [email protected]
Copyright 2010, Missouri S&T
12
Assignment (15 points)
In a study done by freshman psychology students, a random group of people were asked to give a random number above 10000, and a letter A through H in an effort to find a correlation between how people think of letters and numbers. The psych students have finished administering the survey to the participants and have entered all the data into an Excel spreadsheet; however, they don’t know how to analyze the large amount of data they have. A friend of yours is in this group and knows that you are studying to be an engineer and therefore must be good at things involving math and data, so they’ve come to you for help.
They have 4 things they want include on the spreadsheet:
1. The largest number answered, in cell F2 (3 points)
2. The smallest number answered, in cell F3 (3 points)
3. How many times each letter was answered, for statistical purposes, in cells F4-F11 (4 points)
4. The average of the numbers corresponding to each of the letters, in cells F12-F19 (5 points)
You’ll find the students’ data on the Assignment worksheet of the Lesson01_Assignment.xlsx spreadsheet linked with this tutorial (labeled “Tutorial 1 – Assignment Excel File” on ide20.com). Complete the missing fields on this worksheet, and submit your completed Excel file (.xls or .xlsx) via Blackboard.
Hints:
In order to fulfill the criteria you will need some functions that you do not know, but remember, you can go to the Insert Function Window to search for new functions and for help on any function
To speed up the assignment make sure you are writing one equation and then dragging down to cells below which require similar functions, and then modifying the equations, this way you only need to change one small part of the formula instead of having to retype the entire thing.