Directions
Work through the following practice activities using the Learning Resources when you get stuck. You do not have to submit the practice activities for grading.
Download the practice activity’s solutions at the bottom of this page.
Task 1
Create a spreadsheet with the sample data shown below:
| Region | Store Number | Date | Item Number | Sale Price | Quantity |
|---|---|---|---|---|---|
| Northeast | 200 | 1/2/2015 | 10 | 21.95 | 10 |
| Southeast | 300 | 1/2/2015 | 10 | 22.95 | 14 |
| Southwest | 400 | 1/3/2015 | 10 | 20.95 | 15 |
| Northwest | 500 | 1/4/2015 | 10 | 24.95 | 16 |
| Midwest | 600 | 1/5/2015 | 10 | 26.95 | 20 |
| Northeast | 200 | 3/2/2015 | 12 | 124.95 | 4 |
| Southeast | 300 | 3/3/2015 | 12 | 129 | 3 |
| Southwest | 400 | 3/4/2015 | 12 | 140 | 6 |
| Northwest | 500 | 3/5/2015 | 12 | 160 | 8 |
| Midwest | 600 | 3/6/2015 | 12 | 100 | 2 |
Task 2
Experiment with the following formula:
- COUNT(A2:B11) counts the numbers in the range (ignores blank/empty cells).
- =COUNTA(E2:G11) counts all character strings in a range (ignores blank/empty cells).
- =TODAY() inserts today’s date.
- =AVERAGE(E2:E11) adds the numbers in the range, divides them by the total number of values, then provides the average.
- =MAX(E2:E11) shows the highest value in the range.
- =MIN(E2:E11) shows the lowest value in the range.
- =DAYS(C11,C2) calculates the number of days between the two dates.
- =NETWORKDAYS(C2,C11) calculates the number of workdays (assuming a five-day work week) between the two dates.
- Add spaces to the cell A2 in front and at the end; =TRIM(A2) gets rid of these extra spaces.
- =CONCATENATE(TRIM(B2), “-“, TRIM(A2)) combines/merges different values into a single value and fills it down to see Store-Region.
- Add a Total Sale column as column G in the spreadsheet. Compute G2 using the formula: =E2*F2.
- Compute the total sales by item number. Create cells as shown below:
| Item Number | Total Sale |
|---|---|
| 10 | $1,793.25 |
| 12 | $3,206.80 |
Use the formulas =SUMIF(D2:D11, “10”, G2:G11) and =SUMIF(D2:D11, “12”, G2:G11).
- Compute total sales by each region. Use the formula =SUMIF(A2:A11, “Northeast”, G2:G11).
- Compute total sales by each store. Create your own SUMIF formula.
- IF Statement: Add column “Profit (200 at 10%, others at 15%)” as column H. Compute profit as follows: For store 200, profit is at 10% of the total sale; for other stores, profit is at 15% of the total sale.
Formula to use in H2: =IF(B2=200, G2*10%, G2*15%). - Nested IF Statement: Add column “Profit (200 at 10%, 300 at 15%, others at 20%)” as column I. Compute profit as follows: For store 200, profit is at 10% of the total sale; for store 300, profit is at 15%; for other stores, profit is at 20% of the total sale.
Formula to use in I2: = =IF(B2=200, G2*10%, IF(B2=300, G2*15%, G2*20%)). - Add more columns and compute profits in a different way: If the sale price is less than $25, profit is at 10% of the total sale; if the sale price is between $25 and less than $125, profit is at 15% of the total sale; for sale prices at or above $125, profit is at 20% of the total sale.
- Rename Sheet1 as “Sales Data.”
- Add a new worksheet and name it “Sales Charts.”
- Add “Region Total Sales” in A1 and A2 of the Sales Data worksheet.
- Start with using the values in a different worksheet. Work to reference cells from another worksheet. To do this, bring data from Sales Data to Sales Charts. In cell A2 of Sales Charts, type the following formula: =’Sales Data’!F21.
(Note: In your spreadsheet, the cell may be something other than F21). - Drag and drop the formula from A2 to other cells so that you get all the regions and their total sales from “Sales Data” to “Sales Charts.”
- Add a bar chart with Region and Total Sale. Change title to “Total Sales by Region.” Experiment with different fonts and colors.
- Add a pie chart on the Same Data; right-click and choose “Add Data Labels.”
- Select the pie chart, right-click, and choose “Format Data Labels.” Choose Percentage in addition to Values.
- Add a doughnut chart on the Same Data; right-click and choose “Add Data Labels” or “Add Data Callouts.”
- Select the doughnut chart, right-click, and choose “Format Data Labels.” Choose Value in addition to Percentage.
- Add a line graph. Right-click and choose “Add Data Labels” or “Add Data Callouts.”
- Select the line graph. Right-click and choose “Add Trend Line.”
- Repeat Exercises 21 through 30 for “Total Sales by Store” and “Total Sales by Item” (about 10 minutes for each).
- SUMIF another example: Use a different worksheet and enter the following data:
| Property Value | Commission |
|---|---|
| $100,000.00 | $7,000.00 |
| $200,000.00 | $14,000.00 |
| $300,000.00 | $21,000.00 |
| $400,000.00 | $28,000.00 |
Experiment with the following formula:
| Formula | Description | Result |
|---|---|---|
| =SUMIF(A2:A5,”>160000″,B2:B5) | Sum of the commissions for property values over 160,000 | $ 63,000.00 |
| =SUMIF(A2:A5,”>160000″) | Sum of the property values over 160,000 | $ 900,000.00 |
| =SUMIF(A2:A5,300000,B2:B5) | Sum of the commissions for property values equal to 300,000 | $ 21,000.00 |
| =SUMIF(A2:A5,”>” & C2,B2:B5) | Sum of the commissions for property values greater than the value in C2 | $ 49,000.00 |
- SUMIF another example: Use a different worksheet and enter the following data:
| Category | Food | Sales |
|---|---|---|
| Vegetables | Tomatoes | $2,300.00 |
| Vegetables | Celery | $5,500.00 |
| Fruits | Oranges | $800.00 |
| Butter | $400.00 | |
| Vegetables | Carrots | $4,200.00 |
| Fruits | Apples | $1,200.00 |
Experiment with the following formula:
| Formula | Description | Result |
|---|---|---|
| =SUMIF(A2:A7,”Fruits”,C2:C7) | Sum of the sales of all foods in the Fruits category | $ 2,000.00 |
| =SUMIF(A2:A7,”Vegetables”,C2:C7) | Sum of the sales of all foods in the Vegetables category | $ 12,000.00 |
| =SUMIF(B2:B7,”*es”,C2:C7) | Sum of the sales of all foods that end in es (Tomatoes, Oranges, and Apples) | $ 4,300.00 |
| =SUMIF(A2:A7,””,C2:C7) | Sum of the sales of all foods that do not have a category specified | $ 400.00 |
- On separate worksheets, construct pivot tables for total sales by item and for total sales by region.
