Exercise 1 Locker Control Panel Algorithm The School of Fine Arts has 100 lockers numbered 1-100 and their status is displayed on a simulated control console in Excel. At present, all the lockers are open (signified by both the word OPEN and the green color).

We begin by closing every locker whose number evenly divides by 3.

We then close every locker whose number is both divisible by 5 and even while making sure that lockers whose number is divisible by 3 remain closed. How many lockers are closed and how many are open at the end? Directions: In the cell next to the locker number where it says OPEN create an IF() formula that does the following tests: 1.

Is the locker # divisible by 3? If yes, set cell to CLOSED (e.g., the corresponding cell where it says OPEN should now say CLOSED) 2. If no, is the locker divisible by 5 and even? If yes, set cell to CLOSED 3. Otherwise set to OPEN

To create this formula youll need to make use of the following EXCEL functions in addition to IF(): 4. OR() 5. AND() 6. ISEVEN() 7. MOD() a. MOD() takes two arguments: a Number (in this case the locker number) and a Divisor (in this case the number you want to divide by 3 or 5) and returns the remainder of the division. For example, if the locker number is 1, and the divisor is 3, the function MOD(1,3) returns a 1. When a number is evenly divisible by another number, MOD() will return a 0.