Loading in 5 sec....

CS 105 Functions, IF FunctionsPowerPoint Presentation

CS 105 Functions, IF Functions

- 113 Views
- Uploaded on

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

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

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

- 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

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

- 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 this!

- Can I afford it?
- How do I calculate for:
- rebates
- down payments
- interest rates
- years of loan

CS 105 Fall 2005

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… this!

CS 105 Fall 2005

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 this!

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

How the IF function works: this!

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

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

Execute statement this!

after first comma

Flow chart of an If statementIf

Condition

False

True

Execute statement after

second comma

CS 105 Fall 2005

Example of IF this!

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

B3>=100000?

FALSE

TRUE

.06*B3

.05*B3

CS 105 Fall 2005

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 this!

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

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 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 this! Allows Excel to look up a value in a table and return a related value Requires three arguments:

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

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

- 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) this!

Col.2 of LOOKUP table

Cell to evaluate

Lookup Table is E13:F17

Use vlookup.xls spreadsheet

CS 105 Fall 2005

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

Download Presentation

Connecting to Server..