Microsoft

Need assitance 

Dunder-Mifflin

Yearly Report

Yearly Report

Salary

Total

Tim’s Taco’s
Employees	Starting Salary	Gross Sales	Expenses	Promotions	Commission		Total
Bob	35000	99832.23	55232.63	6170.77
Linda	35550	104637.74	62978.87	8725.53
Tina	600	9875.22	4563.01	1456.09
Belcher	400	6563.18	3232.77	1200.43
Louise	300	19532.97	8765.15	1434.24
Average
Highest
Lowest

INSTRUCTIONS
Step 1: Step 2: Apply the Accounting Number Format to Cells B4:G12
Step 3: Apply the Total Style to Cells A9:G9
Step 4: Select Cell F4; enter the formula to calculate Commission
Commmission = (Gross Sales – Expenses- Promotions) * 10%
Step 5: Use Cell F4’s fill handle to copy the Commission formula through F8
Step 6: Select Cell G4; enter the formula to calculate Total Salary
Total Salary = Starting Salary + Commission
Step 7: Use Cell G4’s fill handle to copy the Total Salary formula through G8
Step 8: Hide the “$” symbol for Cells B5:G8
Step 9: In Cell B9, use a function to calculate the sum of Cells B4:B8
Step 10: In Cell B10, use a function to calculate the average of Cells B4:B8
Step 11: In Cell B11, use a function to determine the highest value of Cells B4:B8
Step 12: In Cell B12, use a function to determine the lowest value of Cells B4:B8
Step 13: Select Cells B9:B12; use the fill handle to copy the functions through G9:G12
Step 14: If needed, resize columns to ensure content in cells is visible (not hidden)

Logic in Excel

Using

Logical Operators

[footnoteRef:1] [1: www.fiveminutelessons.com]

A lot of work in Excel involves comparing data in different cells. When you make a comparison between two values, you want to know one of these things:

· Is value A equal to value B (A=B)

· Is A greater than B (A>B)

· Is A less than B (A

· Is A greater than or equal to B (A>=B)

· Is A less than or equal to B (A<=B)

· Is A not equal to B (A<>B)

These are called logical or Boolean operators because there can only be two possible answers in any given case – TRUE or FALSE.

Using Logical Operators in your formulas

Excel is very flexible in the way that these logical operators can be used. For example, you can use them to compare two cells, or compare the results of one or more formulas. For example:

· =A1=A2

· =A1=(A2*5)

· =(A1*10)<=(A2/5)

As these examples suggest, you can type these directly into a cell in Excel and have Excel calculate the results of the formula just as it would do with any formula. With these formulas, Excel will always return either TRUE or FALSE as the result in the cell.

A common use of logical operators is found in Excel’s IF function. The IF function works like this:

=IF(logical_test,value_if_TRUE,value_if_FALSE)

In essence, the IF function carries out a logical test (the three examples above are all logical tests) and then return the appropriate result depending on whether the result of the test is true or false. For example:

· =IF(A1>A2,”Greater than”,”Less than”)

· =IF(A1>A2,A1*10%,A1*5%)

However, you don’t always need to use an IF formula. Here’s a version of this formula that uses a logical operator, and also demonstrates another useful feature of logical operators in general:

· =(A1>A2)*(A1*10*)+(A1<=A2)*(A1*5%)

It looks confusing, but in fact it is very logical (excuse the pun). However, it helps to know that in Excel, TRUE is the same as 1, and FALSE is the same as 0.

So, in this example…

If A1>A2 is TRUE, then the formula will multiple (A1*10%) by 1.

Because A1>A2 is TRUE then A1<=A2 is false, so it will then multiply (A1*5%) by 0.

It will then add the results together: (A1*10%)*1 + (A1*5%)*0.

The result is whatever (A1*10%) equals in the specific example.

Obviously, if A1 is less than A2, then the reverse of this would occur.

Using Multiple Logical Operators

In some cases, you may want to perform more than one comparison as part of your formula. For example:

· (Today is Wednesday) and (Sky is Blue)

· (Today is Wednesday) or (Sky is Blue)

· (Today is Wednesday and (Sky is NOT Blue)

· (Today is Wednesday) or (Sky is NOT Blue)

In Excel, you can use one of three logical functions to construct these formulas:

· AND

· OR

· NOT

The AND function works by performing multiple comparison tests and then returning TRUE if all of the tests were true, and FALSE if one or more of the tests were false. Here are a couple of examples:

· =AND(A1>A2,A1

· IF(AND(A2>A2,A1

The OR function works in a very similar way to the AND function. However, whereas AND requires that all tests return true, the OR function will return TRUE if only one of the tests return true. For example:

· =OR(A1>A2,A1A2 OR A1>A3 is true, then return TRUE. If neither are true, return FALSE).

· =IF(OR(A1>A2,A1

It is important to note that the AND the IF functions can both incorporate up to 255 logical tests (my examples here have only used 2). Regardless of the number of tests you include, the same rules apply as they did in my simple examples.

It is also worth noting that you can combine the AND and OR functions in a single formula. For example:

· =AND(OR(A1>A2,A1A4)

In this example, the AND function will only return TRUE if either (A1>A2 OR A1A4

The final logical function you can use is the NOT function. The NOT function is somewhat self-explanatory – it takes any logical test result and does the opposite. For example:

· =(Sky is Blue) – will return TRUE if the sky is blue, and FALSE if the sky is not blue.

· =NOT(Sky is Blue) will return FALSE if the sky is blue, and TRUE if the sky is not blue.

Note that this example doesn’t care what other colors the sky might be!

Of course, you can use the NOT function with the AND, OR and IF functions:

· =NOT(AND(A1>A2,A1A2 AND A1

· =AND(NOT(A1>A2),A1A2 AND A1

Note that writing NOT(A1>A2) is another way of writing (A1<=A2). In this simple example, using a NOT function didn't add much value, but in some cases the NOT function can be very handy.

In summary, a lot of what you do in Excel, particularly once you start using IF functions, involves using logical operators. The logical functions, AND, OR and NOT are a great way to extend your use of logical operators to perform more complex calculations.

Quick Reference

Logical Operators

< less than

Example: =(A1

> greater than

Example: =(A1>A2) – Returns true if A1 is greater than A2.

<= less than or equal to

Example: =(A1<=A2) – Returns true if A1 is less than or equal to A2.

>= greater than or equal to

Example: =(A1>=A2) – Returns true if A1 is greater than or equal to A2.

<> not equal

Example: =(A1<>A2) – Returns true if A1 is not equal to A2.

= equal

Example: =(A1=A2) – Returns true if A1 is equal to A2.

Logical functions in Excel

AND – Logical “and”. Returns true if all arguments evaluate to true.

Example: =AND(A1>A2, B1

OR – Logical “or”. Returns true if all arguments evaluate to true.

Example: =OR(A1>A2, B1

XOR – Exclusive “or”. Returns true if one, but not both, of the arguments are true.

Example: =XOR(A1>A2, B1

NOT – Logical “not”. Returns the reversed value of the argument.

Example: = NOT(A1>A2) – Returns true if cell A1 is NOT greater than A2. Not that this is not logically equivalent to A1

IF – Returns the first preset value if an argument is true, the second if it is false.

Example: =IF(A1>100,10,0) – Returns 10 if A1 is greater than 100, 0 if it is not.

Lab Instructions

Download the Bob’s Dice Shop Worksheet

1. Using the above functions, write a formula in the cell to the right of each proposition that will evaluate the proposition to true or false. Do not answer true or false, you must write a formula that returns the correct answer. No credit will be given for manually evaluating the statement and typing true or false.

2. Bob receives a bonus of $50 from the d20 manufacturer each time his sales exceed $500 in a quarter. Write a conditional statement (IF) in cell B11 that will display 50 if the d20 sales are greater than $500 and 0 otherwise. Once you have the formula correct, copy it into C11-E11.

3. Bob need to order extra of any type of dice that sold more than $650 for the year. Write a conditional statement in cell G4 that displays “Reorder!” if the sales were more than $650 and “Hold” if the sales were $650 or less. Once you have the formula correct, copy it into G5 – G9.

4. Extra Credit: Use conditional logic to create a conditional formatting rule that will color every other row light green.

5. Save the worksheet as FirstnameLastnameHW28 and submit to the homework 18 drop box.

Sheet1

	Bob’s Dice Shop
Quarterly Sales
Q1	Q2	Q3	Q4	Total per Category
d4	$257.35	$234.98	$102.66	$186.99	$781.98
d6	112.22	145.47	132.74	221.33	611.76
d8	87.65	108.00	299.35	167.54	662.54
d10	99.53	45.44	88.23	285.22	518.42
d12	67.99	54.73	45.77	88.00	256.49
d20	305.22	442.77	567.99	789.29	2,105.27
Total for Quarter	$929.96	$1,031.39	$1,236.74	$1,738.37
Quarter one total sales equal quarter four sales.
Quarter three total sales are at least as much as quarter two total sales.
Quarter two and quarter three total sales are greater than $1000
We sold more than $100 of either d6 or d12 in quarter one.
We sold more than $100 of either d6 or d20 in quarter one, but not both.
Quarter one d4 sales are greater than quarter one d20 sales.
We did not sell the same amount of d4 in quarters one and two.
Quarter three total sales are the same or less than quarter two.
Quarter one total sales are less than quarter four total sales.
Quarter two total sales are greater than quarter one, and less than quarter 3.