Excel Advanced: Functions

This class should have a lot of class involvement.  Let the class come up with the functions themselves.  There are many ways to create each function, so let them try out their ideas and guide them as necessary.  There are some different solutions at the bottom of the Activities page as well as on the handouts page.

Main Objective: Students will be able to confidently search for functions or create their own.

Objectives

  • Perform logical tests with IF, AND, OR Functions
  • Create nested functions
  • Create custom formatting to change color of cells with certain values
  • Build a custom function
  • Prayer
  • Overview of the class
  • Logical Functions
  • Nested Functions
  • Conditional Formatting
  • Custom Function

Outline

This class should have a lot of class involvement.  Let the class come up with the functions themselves.  There are many ways to create each function, so let them try out their ideas and guide them as necessary.  There are some different solutions at the bottom of the Activities page as well as on the handouts page.

Intramurals

You are a student with two jobs.  You work for BYU Intramural athletics as a supervisor as well as working as a TA for a professor.  You want an easy way to see if people who come to play in the tournament are elligible to play.  The requirements are:

  • Currently enrolled at BYU or paid a $20 fee
  • Clean shaven
  • Played in at least one regular season game

Use Logic functions to determine if each player can play.

Discuss with the class what logic functions are and ask how we could use the functions AND / OR in our example.

Grades

Your professor had you make a gradebook to keep track of student’s grades and to calculate their final grade.  The semester is over and the final grades need to be calculated.  First, we need to know more about how the class is structured.

  • 13 homework assignments worth 20 points each
  • 2 midterms 100 points each
  • 1 final 100 points
  • There are 4 readings for the class.  Each student must complete 2 in order to get class participation points.  If a student completes all 4, they receive 1% extra credit.
  • There are 2 projects for the class.  Each student must complete one of the projects which will be graded out of 100 points.  If a student completes both projects they receive 2% extra credit.
  • Homework 20%
  • Class participation 10%
  • Project 15%
  • Midterm 1 15%
  • Midterm 2 15%
  • Final 25%

Come up with some formulas to calculate the project score, extra credit, final grade, and letter grade.

Your professor would also like to see which assignments and tests each student is missing.  Use conditional formatting to highlight missing assignments in red and missing tests in green.

Create Your Own Function

Your professor also has you making some calculations over and over.  Unfortunately, Excel does not have the functions that you need for the calculations.  You can create your own functions in Excel.

 

Alt+F11 will open the visual basic editor.

Insert -> Module

You will first name the function and then write in the formula.  Let’s do one together.

Do the quadratic equation as a class.  Since there are 2 answers to the quadratic equation we have to make two functions

 

Function Quad1(a, b, c)

If (b*b – (4*a*c))>0 Then

Quad1=”Error”

Else

Quad1 = (-b + Sqr((b * b) – 4 * a * c)) / (2 * a)

End If

End Function

 

Function Quad2(a, b, c)

If (b*b – (4*a*c))>0 Then

Quad1=”Error”

Else

Quad1 = (-b – Sqr((b * b) – 4 * a * c)) / (2 * a)

End If

End Function

 

Solutions

Intramurals

Eligible column

=IF(AND(OR(B4=”Yes”,C4=”Yes”),D4=”Yes”,E4>0),”Yes”,”No”)

Gradebook

Project Score

English: =IF w2 is greater than x2, print w2, or else print x2

Excel:    =IF(V2>W2,V2,W2)

=MAX(V2, W2)

Extra Credit

English:

Excel:    =SUM(IF(AND(R2=”x”,S2=”x”,T2=”x”,U2=”x”),0.01,0),IF(AND(V2<>0, W2<>0),0.02,0))

Final Grade

English

Excel:  =SUM(((SUM(B2:N2)/260)*0.2),((O2/100)*0.15),((P2/100)*0.15),(IF(COUNTIF(R2:U2,”x”)>1,0.1,0)),((X2/100)*0.15),((Q2/100)*0.25),Z2)

Letter

English:

Excel:    =IF(AA>0.9,”A”,IF(AA>0.8,”B”,IF(AA>0.7,”C”,IF(AA>0.6,”D”,”F”))))

Formatting

formatting option, select all the student names on the left and create a new conditional formatting rule.  Select “Use a formula…”

Enter the following: =AND(ISBLANK($V2), ISBLANK($W2))

 

Create a Custom Function

Temp:

Function temperature(celcius)

temperature = (9 / 5) * celcius + 32

End Function

 

Hex:

Function hex(side)

hex = ((3 * Sqr(3)) / 2) * side ^ 2

End Function