CS 105 Functions, IF Functions

1. CS 105Functions, IF Functions What is a function? What is an IF function? A PMT function? What is a condition? What is nesting? CS 105 Spring 2010

2. Functions • An Excel command that does a specific task • Here’s a common function in Excel: SUM(A2:B5) • This function’s task: sum all cell values described within the parentheses (here, the range A2:B5) • the answer is called the function’s return value • Why functions? Define once, use many times • simply change the stuff within parentheses • called the arguments of the function CS 105 Spring 2010

3. Arguments • A function has a name and arguments • Arguments can be data, cell names/ranges or other functions • Examples: SQRT(9) one argument AVERAGE (A3, A5, A7) three arguments SUM (A1:A10, Totals) two arguments (a range and the name of a range) CS 105 Spring 2010

4. Built-in functions • Excel comes pre-loaded with many useful functions • Categories include: • Math & Trig e.g., SQRT, ABS, COUNT, COUNTA,… • Statistical e.g., AVG • Financial e.g. PMT • Text e.g. CONCATENATE • Later, we will see how to write our own functions to extend Excel’s “toolkit” or “library” CS 105 Spring 2010

5. PMT function • PMT function requires interest rate/period, number of payments, and amount of loan • Establish PMT • Amount of loan • Down payment • Interest rate/12 • Term (in months) CS 105 Spring 2010

6. As in… CS 105 Spring 2010

7. Note: • 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 CS 105 Spring 2010

8. Quiz on PMT • Say the interest on a car is 18% a year. In calculating the interest PER MONTH you: • Pay 18% interest each month • Pay 18% divided by 12 • Pay 18% multiplied by 12 CS 105 Spring 2010

9. Using Goal Seek Data Tab in Excel 2007 • 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 Spring 2010

10. IF: adding power to functions • Suppose we want formula #1 under some conditions, and a different formula #2 under other conditions • Example: How does the ABS function work? • ABS(A5) = A5 if A5 >= 0, but • ABS(A5) = -A5 if A5 < 0 • Visually: If A5 >= 0 True False -A5 A5 CS 105 Spring 2010

11. Apply formula #1 (after first comma) The IF function If Condition False True Apply formula #2 (aftersecond comma) CS 105 Spring 2010

12. 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) Note: string data in a formula must be surrounded by “ ”,why? CS 105 Spring 2010

13. 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 Spring 2010

14. 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 CS 105 Spring 2010

15. Example of IF =IF(B3>=100000, .06*B3, .05* B3) B3>=100000? FALSE TRUE .06*B3 .05*B3 CS 105 Spring 2010

16. Nested IFs • 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 Spring 2010

17. 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 Spring 2010

18. Practice with Relational Operators • A1 = B1 -- FALSE • A1 <> B1 -- TRUE • A1 = B2 -- FALSE • A1 < B1 -- TRUE • A1 < B2 -- TRUE • A1 = A2 -- TRUE • A1 >= A2 -- TRUE • A2 > B2 -- FALSE CS 105 Spring 2010

19. AND, OR, NOT Functions Formula view Output of formulas: CS 105 Spring 2010

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

21. 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 Spring 2010

22. Text Functions • Concatenation (putting two words together) • Concatenation can be done by a function • e.g. =CONCATENATE(B3,C4) • Operands can be : • character strings: “Tarzan” & “Jane” • cell addresses containing text values: B3 • Concatenation can also be done by using an operator& e.g. = B3 &C4 CS 105 Spring 2009

23. Adding a space • Strings in formulas must 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 Spring 2009

24. To Summarize • What is a function? • What is an IF function? A PMT function? • What is a condition? • What is nesting? CS 105 Spring 2010