1 / 30

CS 105 Functions, IF Functions

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)

hayden-weiss
Download Presentation

CS 105 Functions, IF Functions

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. Functions • We already covered functions, with SQL. Here’s a common function in Excel: =SUM(A2:B5) • Functions are available for various categories: • Date & Time • Financial • Statistical • Math & Trig • etc. CS 105 Fall 2005

  3. 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

  4. 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

  5. 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

  6. 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

  7. Analysis of a Car Loan • Can I afford it? • How do I calculate for: • rebates • down payments • interest rates • years of loan CS 105 Fall 2005

  8. PMT function • 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

  9. As in… CS 105 Fall 2005

  10. Using Goal Seek • 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

  11. Practice with Relational Operators A B 1 • A1 = B1 -- FALSE • A1 <> B1 -- ________ • A1 = B2 -- TRUE • A1 < B1 -- ________ • A1 < B2 -- FALSE • A1 > A2 -- TRUE • A1 >= B2 -- _________ • A2 > B2 -- FALSE 2 CS 105 Fall 2005

  12. AND, OR, NOT Functions Formula view Output of formulas: CS 105 Fall 2005

  13. How the IF function works: eg, single IF statement looks like this: IF(A2>90,“A”,“B”) IF (logical test, value if true, value if false) CS 105 Fall 2005

  14. Commission example 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

  15. Example IF function wizard 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

  16. Execute statement after first comma Flow chart of an If statement If Condition False True Execute statement after second comma CS 105 Fall 2005

  17. Example of IF =IF(B3>=100000, .06*B3, .05* B3) B3>=100000? FALSE TRUE .06*B3 .05*B3 CS 105 Fall 2005

  18. Nested IFs • 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

  19. Example 2 of IF B3>=100000? FALSE? Then another test... TRUE B3>=80000? .06*B3 TRUE FALSE .035* B3 .05*B3 =IF(B3>=100000, .06*B3, IF(B3>=80000, .05*B3, .035*B3)) CS 105 Fall 2005

  20. Seeing the formulas • Click on Control and ` (the same key as tilde ~) CS 105 Fall 2005

  21. COUNTIF Function • Syntax: COUNTIF(Range, Criteria) • Can be used on a summary worksheet, as in lab Download employee.xls to practice this CS 105 Fall 2005

  22. SUMIF Function • 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

  23. VLOOKUP, HLOOKUP Function • 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

  24. =VLOOKUP(C18,E13:F17,2) Col.2 of LOOKUP table Cell to evaluate Lookup Table is E13:F17 Use vlookup.xls spreadsheet CS 105 Fall 2005

  25. String Operations • 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

  26. Concatenating -- Using & Using the wizard to enter a function: • As in MySQL, you can add a space between each word. CS 105 Fall 2005

  27. Adding a space • 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

  28. Providing User Feedback • Some functions are built into Excel to help provide feedback and test content • They could be helpful to you… CS 105 Fall 2005

  29. Examples of useful functions =ISERROR(B2) equals TRUE =ISNUMBER(B3) equals FALSE =ISTEXT(B4) equals TRUE =ISBLANK(B5) equals TRUE Formula view: CS 105 Fall 2005

  30. How to use these functions • 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

More Related