Microsoft Office-Excel Logic Excercises, Exercises of MS Microsoft Excel skills

Download Microsoft Office-Excel Logic Excercises and more Exercises MS Microsoft Excel skills in PDF only on Docsity!

Excel Logic Exercises

for Duke’s

Fuqua School of Business

Decision Models Course

Paper based logic

exercises for Excel.

Contents

• Exercise 1: Using AND, OR, and NOT functions ......................... Page
• Exercise 2: Using the IF function ..........................................
• Exercise 3: More practice with the IF function .........................
• Exercise 4: IF function practice (from the Proficiency Exercises) .........
• Exercise 5: Building and using a Nested IF statement ................
• Exercise 6: IF, MIN, and SUMPRODUCT ...................................
• Exercise 7: Write a formula for multiple conditions ..................
• Answer Key ....................................................................

Using Logical Functions in Modeling

Exercise 1. Using Excel’s AND, OR, and NOT Functions

File: LogicPractice.xls, Worksheet: “AND OR NOT”

A

(^1) The Data

(^2) 15

(^3) 9

(^4) 8

A. Write an AND formula to determine if A2>A3 and A2<A4 is a true or false statement.

B. Write an OR formula to determine if A2>A3 or A2<A4 is a true or false statement.

C. Write a formula that expresses that A2+A3=24 is a false statement.

Logic Practice on Paper – Using Logical Functions in Modeling

Exercise 2. Using Excel’s IF Function

File: LogicPractice.xls, Worksheet: “IF”

A

(^1) The Data

(^2) 50

A. Write an IF statement so that if the number in Cell A2 is less than 100 the formula displays the text “Within budget”, otherwise the formula displays the text “Over budget”.

B. Write an IF statement so that if the number in Cell A2 is 100 then the formula sums the range B5:B15. Otherwise, the formula returns a blank (empty text).

Logic Practice on Paper – Using Logical Functions in Modeling

Exercise 4. IF Function Practice (from the Proficiency Exercises)

File: LogicPractice.xls, Worksheet: “Olive Oil Logic -1”

Olive oil can be purchased according to this price schedule:

A B (^1) Cost/gallon for the first 500 gallons $ (^2) Cost/gallon for gallons above 500 $ 3 Number of gallons: 4 10 (^5 ) (^6 ) (^7) 1,

Write IF statements to calculate the cost of the quantities of olive oil listed in Cells A5, A6, and A7 above. (See a sample formula to calculate the cost of 10 gallons (A4) below.)

The syntax of Excel’s IF function is: =IF(condition-to-test, value-if-condition-true, value-if-condition-false)

For example, a formula to find the cost for 10 gallons of olive oil is:

=IF(A4<=500,B1* A4, 500B1+(A4-500)B2) See the footnote on this page.*

A. Write a formula to find the cost of 483 gallons.

B. Write a formula to find the cost of 500 gallons.

C. Write a formula to find the cost of 1,600 gallons.

Note: It’s always a good idea to use cell references instead of constant values in formulas. For the examples here, however, I’ve used some constants to make the formulas easier to read and understand.

Logic Practice on Paper – Using Logical Functions in Modeling

Exercise 5. Building & Using a Nested IF Statement

File: LogicPractice.xls, Worksheet: “Olive Oil Logic -2”

We’ve modified the olive oil price schedule to give an additional price break for quantities over 1,000 gallons. The new pricing schedule is:

A B (^1) Cost/gallon for the first 500 gallons $ 2 Cost/gallon for next 500 gallons $ (^3) Cost/gallon for gallons > 1,000 $ 4 (^5 ) (^6 ) (^7 )

Write two formulas using nested IF statements to calculate the cost of the quantities of olive oil listed in Cell A6 (483 gallons) and Cell A7 (2,001 gallons) above.

An Excel nested IF function can be written with this syntax: =IF(condition-to-test, IF(condition-to-test, value-if-condition-true, value-if-condition-false), value-if-condition-true, value-if-condition-false)

For example, one formula to find the cost for 1,600 gallons is:

=IF(A5<=500,A5$B$1,IF(A5<=1000,(500$B$1)+(A5-500)$B$2,(500$B$1)+(500$B$2)+(A5-1000)$B$3))

A. Write a formula to find the cost of 483 gallons.

B. Write a formula to find the cost of 2,001 gallons.

Exercise 6, Continued

For example, formulas to calculate the cost of 1,600 gallons are located below in Cells G9, G10, G11, and G12.

A. Write the four formulas to calculate the cost of 483 gallons.

B. Write the four formulas to calculate the cost of 2001 gallons.

More Logic Practice

More Logic Practice on Paper – Writing a Formula to Account for

Multiple Conditions

Exercise 7. Calculate Employee Retirement & Health Plan

File: MoreLogicPractice.xls, Worksheet: “Benefit Calculations”

A company contributes to each eligible employee’s retirement plan at the rate of 4% of the employee’s annual salary. However, to be eligible for this benefit, an employee must have full-time status with two or more years of employment. A calculation for the retirement contribution requires a test of two conditions: Full- or part-time status and number of years of employment. A graphical view of the conditions to test might look like this illustration:

File name: MoreLogicPractice.xls

B. Write the formula to calculate the Health Plan Cost for Gopnik. You should be able to copy this formula down the column to get valid values for employees Mahfouz through Heller.

Answer Key for the Paper-And-Pencil Exercises

Exercise 1 - Page 7 Using Excel’s AND, OR, and NOT Functions File: LogicPractice.xls Worksheet: “AND OR NOT” A. =AND(A2>A3, A2<A4) B. =OR(A2>A3, A2<A4) C. =NOT(A2+A3=24)

Exercise 2 – Page 8 Using Excel’s IF function File: LogicPractice.xls Worksheet: “IF” A. =IF(A2<=100,"Within budget","Over budget") B. =IF(A2=100,SUM(B5:B15),"")

Exercise 3 – Page 9 Using IF Functions to Determine Grades File: LogicPractice.xls Worksheet: “IF Scores” A. =IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F")))) B. =IF(A3>89,"A",IF(A3>79,"B", IF(A3>69,"C",IF(A3>59,"D","F")))) C. =IF(A4>89,"A",IF(A4>79,"B", IF(A4>69,"C",IF(A4>59,"D","F"))))

Exercise 4 – Page 10 IF Function Practice (From the Proficiency Exercises) File: LogicPractice.xls Worksheet: Olive Oil Logic - 1 A. =IF(A5<=500,B1* A5, 500B1+(A5-500)B2)B. B. =IF(A6<=500,B1* A6, 500B1+(A6-500)B2) C. =IF(A7<=500,B1* A7, 500B1+(A7-500)B2)

Exercise 5 – Page 11 Building and Using a Nested IF Statement (from the Proficiency Exercises) File: LogicPractice.xls Worksheet: Olive Oil Logic - 2 A. =IF(A6<=500,A6$B$1,IF(A6<=1000,(500$B$1)+(A6- 500)$B$2,(500$B$1)+(500$B$2)+(A6-1000)$B$3)) B. =IF(A7<=500,A7$B$1,IF(A7<=1000,(500$B$1)+(A7- 500)$B$2,(500$B$1)+(500$B$2)+(A7-1000)$B$3))