Problem 16.6

1. select the set of rows you want to conditionally format to alternate be normal or shaded.

2. Click on the format menu and choose "conditional formatting ."

3. Click plus button to add a new rule

4. change the style to adda new rule

5. change the field to "use a formula to determine which cells to format."

6. insert this formula: =mod(row(),20.

7. change the "format with" field to "custom format"

8. Select a medium shade of gray

9. Click OK to close "format Cells window you used to select the gray fill

10. Click OK to close the "new formatting rule" window

11. click OK to close the "manage rules window"

B.

Improve tabular displays by adding "traffic Lights " or other icons in column B (along with the number) to indicate status in terms of "red"= definitely a negative trend, "yellow "=warning , and "green" = definitely a positive trend for sales , sales returns, and the cost of goods sold.

a. For sales: green means that this years sales are larger than last years ; yellow means that this years sales are at least 98% of the prior years; red otherwise

b. For sales returns; Green means that returns are less than 3.5% of sales; yellow means between 3.5% and 4% of sales ; red means 4% of sales or more

c. For operational expenses , green means less than last year ; yellow means less than a 3% increase; red means more than 3% increase over the prior year.

HINT

use the "conditional formatting" toolbar to select :new rule" and then select "icons sets ".

You want to change the rule "type" to format so that you can then compare the value in column B to the other columns

	2014	2013	2012	2011	2010
Sales	953,500	944,000	925,000	930,000	910,000
Sales Returns	45,000	39,000	36,000	33,000	31,000
Net Sales	908,500	905,000	889,000	897,000	879,000
Cost of Goods Sold	710,000	650,000	675,000	730,000	660,000
Gross Profit	198,500	255,000	214,000	167,000	219,000
Operating Expenses	150,000	147,000	145,000	146,000	140,000
Income Taxes	3,800	9,000	4,200	8,700	6,700
Net Income	44,700	99,000	64,800	12,300	72,300