excel formulas ii - west virginia university

Excel Formulas II

3-D Formulas

• 3-D Formulas are used to reference data on another spreadsheet in the same workbook

• Syntax:• =[name of sheet]![cells to reference]

• Reference: Office 3D Formula

Create 3-D Formula (step 1)

• Click on worksheet tab where you want 3-D formula

• Select cell

• Type =

Create 3-D Formula (step 2)

• Click worksheet tab for first reference

• Select desired cell

• Enter arithmetic operator

Operation Symbol in Excel

Addition +

Subtraction -

Multiplication *

Division /

Create 3-D Formula (step 3)

• Click worksheet tab for second reference

• Select desired cell

• Press the Enter key

Create 3-D Formula (step 4)

• You should have a 3-D reference on the worksheet you started with

Excel Logical Functions

• IF

• AND

• OR

• Reference: Office Logical Functions

AND True False

True True False

False False False

OR True False

True True True

False True False

IF True False

Logical Condition What to do if condition is True

What to do if condition is False

Know these Symbols!

IF function

• Excel IF function has three arguments:

• A condition

• What to do if condition is met

• What to do if condition is notmet

• Reference: Office IF Function

= 𝐼𝐹(8 < 10 , "Yes" , "No")condition What to

show if TRUE

What to show if FALSE

Create IF Function (step 1)

• Click in cell where IF statement goes

• Formulas ribbon

• In the Function Library group:• click Logical▼

• IF

Create IF Function (step 2)

• In Function Arguments dialog box:• Logical_test

• Use logical operator

=, <, <=, >, >=, <>

• Value_if_true• What to do if logical condition

is True

• Value_if_false• What to do if logical condition

is False

• Click OK

Create IF Function (step 3)

• The IF will have two results:

• For TRUE

• Logical condition was valid

• For FALSE

• Logical condition was invalid

AND Function

• AND function requires all logic statements to be TRUE to return TRUE

• If one statement is FALSE, then the result is FALSE

• If both statements are FALSE, then the result is FALSE

• Reference: Exploring Office 2013 Volume 2

Excel Chapter 7P. 312

Creating AND Function (step 1)

• Select cell

• Formulas ribbon

• In the Function Library group:• click Logical▼

• AND

Creating AND Function (step 2)

• In Function Arguments dialog box:• Logical1

• Enter the first logical condition

• Logical2• Enter second logical condition

• Logical3• Not needed

• Click OK

Creating AND Function (step 3)

• If both numbers are smaller than 10• TRUE

• If the number in column A is bigger than 10 but the number in column B is smaller than 10• FALSE

• If the number in column B is bigger than 10 but the number in column A is smaller than 10• FALSE

• If both numbers are bigger than 10• FALSE

OR Function

• OR is like the opposite of AND

• As long as one logic statement is TRUE, the result is TRUE

• Reference:

Exploring Office 2013 Volume 2Excel Chapter 7

P. 313

Creating OR Function (step 1)

• Select cell

• Formulas ribbon

• In the Function Library group:• click Logical▼

• OR

Creating OR Function (step 2)

• In Function Arguments dialog box:• Logical1

• Enter the first logical condition

• Logical2• Enter second logical condition

• Logical3• Not needed

• Click OK

Creating OR Function (step 3)

• If both numbers are smaller than 10• TRUE

• If the number in column A is bigger than 10 but the number in column B is smaller than 10• TRUE

• If the number in column B is bigger than 10 but the number in column A is smaller than 10• TRUE

• If both numbers are bigger than 10• FALSE

Nested IF function

• Nested means putting an IF function in another IF function

• Nested IF functions are used to make three or more comparisons

• Reference: Office Nested IF

Creating Nested IF Function (step 1)

• Select cell

• Formulas Ribbon

• In the Function Library group:• click Logical▼

• IF

Creating Nested IF Function (step 2)

• In the Function Arguments dialog box:• Logical_test

• Type in one logical test

• Value_if_true• You may type additional IF

function here (see slide 8)

• Value_if_false• You may type additional IF

function here (see slide 8)

Creating Nested IF Function (step 3)

• In the example to the right:• If both numbers in columns A and B

are less than 10• it prints “Both values less than ten”

• If the number in column A is bigger than 10• It print “First value bigger than ten”

• If the number in column B is bigger than 10• It prints “Second value bigger than ten”

• Reference:

Exploring Office 2013 Volume 2Excel Chapter 7

P. 310