Microsoft Office Tutorials and References
In Depth Information
b. Text from a Word document separated by spaces into appropriate cells of a
Type Bulldog Chihuahua Mutt Poodle Schnauzer Airedale
9. Transpose the table in a. into a worksheet by ﬁrst copying it into the worksheet
then applying the transpose function.
10. Write a logical if statement that will test for the following conditions:
If the cell value in A1 is larger than the cell value in A2 then write in cell
A3—“A1 beats A2”, otherwise write “A2 beats A1”. Test this with values of:
What happens with (3) and can you suggest how you can modify the if
statement to deal with ties?
20 and A2
10; (2) A1
15 and A2
30; (3) A1
15 and A2
If the content of cell A1 is between 0 and 50 write “Class A”; If the content
of A1 is between 51 and 100 write “Class B”; If neither then write “Class
C”. Place the function in cell A2 and test with 23, 56, and 94.
If the content of cell A1 is between 34 and 76 or 145 and 453 write “In
Range”, otherwise write “Out of Range”. Place the function in A2 and test
with 12, 36, 87, 243, and 564. Hint: this will require you to use other logical
functions ( OR , AND ,etc).
If the contents of cell A1>23 and the contents of cell A2<56 then write
“Houston we have a problem” in cell A3. Otherwise write “Onward through
the fog” in cell A3.
11. Sort the data in Table 4.1 as follows:
a. by size of deposit—smallest to largest
b. primarily by account and secondarily by deposit-ascending for both
c. after a. and b., reconstruct the original table by sorting on the ﬁeld you think
12. Filter Table 4.2 as follows:
a. by Salesperson Kenji and Lupe and then between and including 1/10/2005
b. by Salesperson Kenji and Lupe and then the complement (not included in)
of the dates in a., above
c. all sales on the dates 1/06/2005 or 1/31/2005
d. all Ford sales above and including $13,000.
13. Use the Advanced Filter on Table 4.2 as follows:
a. any Toyotas sold by Bill on or after 1/15/2005 or any Fords sold by Piego
on or after 1/20/2005- calculate the subtotal sum, average, and count for the
b. any cars sold for more than $25,000 on or after 1/6/2005 and before or on
1/16/2005 or any cars sold for less than $20,000 on or after 1/26/2005 -