Cs 105 functions if functions
1 / 30

CS 105 Functions, IF Functions - PowerPoint PPT Presentation

  • Uploaded on

CS 105 Functions, IF Functions. What is an IF function? A PMT function? What is a condition? What is a Nested IF ? Concatenation in Excel. Course Guide p. 125. Functions. We already covered functions, with SQL. Here’s a common function in Excel: = SUM(A2:B5)

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' CS 105 Functions, IF Functions' - hayden-weiss

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Cs 105 functions if functions

CS 105Functions, IF Functions

What is an IF function? A PMT function?

What is a condition?

What is a Nested IF?

Concatenation in Excel

Course Guide p. 125


  • We already covered functions, with SQL. Here’s a common function in Excel:


  • Functions are available for various categories:

    • Date & Time

    • Financial

    • Statistical

    • Math & Trig

    • etc.

CS 105 Fall 2005

Arguments akin to sql
Arguments – akin to SQL

  • A function has name and arguments

  • Argumentsare constants, cell addresses/ranges or other functions

  • Examples--

    SQRT (A12) one argument

    AVERAGE (A3, A5, A7) three arguments

    SUM (A1:A10, B2:C4) two arguments (each is a range)

CS 105 Fall 2005

Miscellaneous functions
Miscellaneous Functions

  • ABS displays the absolute value

  • COUNTgives the number of cells that contain numbers in a range

  • ____________gives the number of cells that contain numbers or text entries in a range (simplified definition)

  • IF performs a calculation based on a condition being met

  • PMT computes the amount of loan payments.

CS 105 Fall 2005

We can create our own functions
We can create our own functions

  • Reverse(strWord) is at the heart of the function

  • The function Reverse wants an argument, either a cell address or a word, and then it will reverse the letters in the word

CS 105 Fall 2005

We can create our own functions don t try to understand this
We can create our own functions - don't try to understand this!

  • Public Function Reverse(strWord As String) As String'This function reverses the letters in a word Dim intLastLetter As Integer Dim intCounter As Integer Reverse = "" intLastLetter = Len(strWord)For intCounter = intLastLetter To 1 Step -1 Reverse = Reverse & Mid(strWord, intCounter, 1) Next intCounterEnd Function

CS 105 Fall 2005

Analysis of a car loan
Analysis of a Car Loan this!

  • Can I afford it?

  • How do I calculate for:

    • rebates

    • down payments

    • interest rates

    • years of loan

CS 105 Fall 2005

Pmt function
PMT function this!

  • PMT function requires interest rate/period, number of payments, and amount of loan

  • Watch out! Repayment is by default from the bank’s point of view (returns a negative number, and we want a positive number).

  • In order to find our payment, we make the final number negative

  • Establish PMT

    • Amount of loan

    • Down payment

    • Interest rate/12

    • Term (in months)

CS 105 Fall 2005

As in
As in… this!

CS 105 Fall 2005

Using goal seek
Using Goal Seek this!

  • I want to pay only $200 a month. What car can I afford?

    • Change payment total by changing one of the above variables

    • The top value must have a formula in it

    • Goal: $200

    • Computer calculates the value of the car you can afford

CS 105 Fall 2005

Practice with relational operators
Practice with Relational Operators this!




  • A1 = B1 -- FALSE

  • A1 <> B1 -- ________

  • A1 = B2 -- TRUE

  • A1 < B1 -- ________

  • A1 < B2 -- FALSE

  • A1 > A2 -- TRUE

  • A1 >= B2 -- _________

  • A2 > B2 -- FALSE


CS 105 Fall 2005

And or not functions
AND, OR, NOT Functions this!

Formula view

Output of formulas:

CS 105 Fall 2005

How the if function works
How the IF function works: this!

eg, single IF statement looks like this:


IF (logical test, value if true, value if false)

CS 105 Fall 2005

Commission example
Commission example this!

If TEESALES sells $100,000 worth of tee shirts, the sales commission rate will be 6%. If the sales total is less than $100,000, the rate will be only 5%.

Finding the Commission

for sales >= $100,000, pay 6% of sales

for sales < $100000, pay 5% of sales

CS 105 Fall 2005

Example if function wizard
Example IF function wizard this!

An IF function tests for a condition

You can type the IF function directly in the formula

box or use the dialog box of the IF Function

Course Guide p. 128

CS 105 Fall 2005

Flow chart of an if statement

Execute statement this!

after first comma

Flow chart of an If statement





Execute statement after

second comma

CS 105 Fall 2005

Example of if
Example of IF this!

=IF(B3>=100000, .06*B3, .05* B3)






CS 105 Fall 2005

Nested ifs
Nested IFs this!

  • An IF function may contain another IF as its first, second, or both arguments-- this is called nested.

  • If TEESALES sells $100,000 worth of tee shirts, the sales commission rate will be 6%. If the sales total is equal to or more than $80,000, but less than $100,000, the rate will be 5%. If total sales are below $80,000, the rate will be 3.5% of sales.

CS 105 Fall 2005

Example 2 of if
Example 2 of IF this!


FALSE? Then another test...






.035* B3


=IF(B3>=100000, .06*B3, IF(B3>=80000, .05*B3, .035*B3))

CS 105 Fall 2005

Seeing the formulas
Seeing the formulas this!

  • Click on Control and ` (the same key as tilde ~)

CS 105 Fall 2005

Countif function
COUNTIF Function this!

  • Syntax: COUNTIF(Range, Criteria)

  • Can be used on a summary worksheet, as in lab

Download employee.xls to practice this

CS 105 Fall 2005

Sumif function
SUMIF Function this!

  • The SUMIF function adds figures to a total only if they match a certain criterion

  • The syntax is

    SUMIF( range, criteria to check, range where numbers are)

CS 105 Fall 2005

Vlookup hlookup function
VLOOKUP, HLOOKUP Function this!

  • assigns a value to a cell based on a numeric value in another cell

  • Allows Excel to look up a value in a table and return a related value

    • for example, Excel can look up your average in this class and return your grade

  • Requires three arguments:

    • the numeric value (or cell) to look up

    • the range of the table

    • the column number containing the value you want to return

  • CS 105 Fall 2005

    Vlookup c18 e13 f17 2
    =VLOOKUP(C18,E13:F17,2) this!

    Col.2 of LOOKUP table

    Cell to evaluate

    Lookup Table is E13:F17

    Use vlookup.xls spreadsheet

    CS 105 Fall 2005

    String operations
    String Operations this!

    • Concatenation (putting two words together)

    • Operator is &

      e.g. = B3 & C4

    • Concatenation can also be done by a function

      • e.g. =CONCATENATE(B3,C4)

    • Operands can be :

      • character strings: “Tarzan” & “Jane”

      • cell addresses containing text values: B3 & B4

    CS 105 Fall 2005

    Concatenating using
    Concatenating -- Using & this!

    Using the wizard to enter a function:

    • As in MySQL, you can add a space between each word.

    CS 105 Fall 2005

    Adding a space
    Adding a space this!

    • Strings always appear in quotes in Excel.

    • To add a space, just put it between quotes.

    • Remember, there is an ASCII value for a space!

    • You can also put a space between the quote and the start of Water

    CS 105 Fall 2005

    Providing user feedback
    Providing User Feedback this!

    • Some functions are built into Excel to help provide feedback and test content

    • They could be helpful to you…

    CS 105 Fall 2005

    Examples of useful functions
    Examples of useful functions this!

    =ISERROR(B2) equals TRUE

    =ISNUMBER(B3) equals FALSE

    =ISTEXT(B4) equals TRUE

    =ISBLANK(B5) equals TRUE

    Formula view:

    CS 105 Fall 2005

    How to use these functions
    How to use these functions this!

    • You want to calculate the average of the range C1:C4, but you can't be sure that the cells contain numbers.

    • The formula AVERAGE(C1:C4) returns #DIV/0!

      if C1:C4 does not contain any numbers. Use:

      =IF(ISERROR(AVERAGE(C1:C4)),"No Numbers",AVERAGE(C1:C4))

    CS 105 Fall 2005