1 / 24

CS 105 Functions, IF Functions

CS 105 Functions, IF Functions. What is a function? What is an IF function? A PMT function? What is a condition? What is nesting ?. Functions . An Excel command that does a specific task Here’s a common function in Excel: SUM(A2:B5)

mick
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 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

More Related