Download - Excel Formulas II - West Virginia University
![Page 1: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/1.jpg)
Excel Formulas II
![Page 2: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/2.jpg)
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
![Page 3: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/3.jpg)
Create 3-D Formula (step 1)
• Click on worksheet tab where you want 3-D formula
• Select cell
• Type =
![Page 4: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/4.jpg)
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 /
![Page 5: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/5.jpg)
Create 3-D Formula (step 3)
• Click worksheet tab for second reference
• Select desired cell
• Press the Enter key
![Page 6: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/6.jpg)
Create 3-D Formula (step 4)
• You should have a 3-D reference on the worksheet you started with
![Page 7: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/7.jpg)
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!
![Page 8: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/8.jpg)
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
![Page 9: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/9.jpg)
Create IF Function (step 1)
• Click in cell where IF statement goes
• Formulas ribbon
• In the Function Library group:• click Logical▼
• IF
![Page 10: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/10.jpg)
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
![Page 11: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/11.jpg)
Create IF Function (step 3)
• The IF will have two results:
• For TRUE
• Logical condition was valid
• For FALSE
• Logical condition was invalid
![Page 12: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/12.jpg)
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
![Page 13: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/13.jpg)
Creating AND Function (step 1)
• Select cell
• Formulas ribbon
• In the Function Library group:• click Logical▼
• AND
![Page 14: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/14.jpg)
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
![Page 15: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/15.jpg)
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
![Page 16: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/16.jpg)
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
![Page 17: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/17.jpg)
Creating OR Function (step 1)
• Select cell
• Formulas ribbon
• In the Function Library group:• click Logical▼
• OR
![Page 18: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/18.jpg)
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
![Page 19: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/19.jpg)
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
![Page 20: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/20.jpg)
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
![Page 21: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/21.jpg)
Creating Nested IF Function (step 1)
• Select cell
• Formulas Ribbon
• In the Function Library group:• click Logical▼
• IF
![Page 22: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/22.jpg)
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)
![Page 23: Excel Formulas II - West Virginia University](https://reader031.vdocuments.us/reader031/viewer/2022013015/61d0538d393c8a7ecd3a6363/html5/thumbnails/23.jpg)
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