Microsoft Office Tutorials and References

In Depth Information

b. Text from a Word document separated by spaces into appropriate cells of a

workbook:

Observation123456

Type Bulldog Chihuahua Mutt Poodle Schnauzer Airedale

Age3145797

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:

a.

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:

(1) A1

15.

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

=

b.

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.

c.

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).

d.

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

is best.

12. Filter Table 4.2 as follows:

a. by Salesperson Kenji and Lupe and then between and including 1/10/2005

and 1/21/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

Amt Paid

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 -

Search JabSto ::

Custom Search