1 / 30

# CS 105 Functions, IF Functions - PowerPoint PPT Presentation

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.

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

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

• 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

• 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

• 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

• 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

• 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

• 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

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

Formula view

Output of formulas:

CS 105 Fall 2005

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

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 statement

If

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

Seeing the formulas this!

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

CS 105 Fall 2005

COUNTIF Function this!

• Syntax: COUNTIF(Range, Criteria)

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

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

• 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

• 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

Col.2 of LOOKUP table

Cell to evaluate

Lookup Table is E13:F17

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

Using the wizard to enter a function:

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

CS 105 Fall 2005

• 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

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

• They could be helpful to you…

CS 105 Fall 2005

=ISERROR(B2) equals TRUE

=ISNUMBER(B3) equals FALSE

=ISTEXT(B4) equals TRUE

=ISBLANK(B5) equals TRUE

Formula view:

CS 105 Fall 2005

• 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