Search for question
Question

1. Open file G4.

2. Open the tab called "Sheet1." Using A, B, C, D, F as headings, using the

"Count If" function find the corresponding data from the raw numbers in cells

K3:K7 from columns B.E.

3. Repeat this step for EACH QUARTER individually. To get full credit you

MUST utilize ABSOLUTE REFERENCING so that cell L3 can be filled over

to 03 and then down to 07 correctly. You will need to LOCK the appropriate

portions of the COUNTIF function. (Hint: you need to lock only the ROW for

the RANGE and only the COLUMN for the CRITERIA.)

4. Then sum your outputs from Q1-Q4 in cells P3:P7. If you do this correctly

you'll get a positive message.

5. Make a pie graph of the Total Grades by letter. Give the chart a title and

legend. Keep in mind you should have only 2 data sets that are being

considered for the pie chart.

6. Right-Click on the pie chart and add "Data-Labels." Once the data labels

appear right click on one of the numbers that appears and format the data

labels to include percentage and category name only. Now that you added data

labels, delete the legend.

7. Use the data in J3:07 to make a CLUSTERED Column Chart by Quarter

(NOTE: you'll want to exclude the TOTALs and SUM data, only

QUARTER 1-4 data). Have a chart title, axis labels, and NO legend. Make

sure that all your titles are visible and not blocked by any part of the chart.

Add DATA LABELS for each Column that include the CATEGORY VALUE

on the OUTSIDE END. (HINT: easiest to just select all of J3:07 and then

once the graph is made click on it and then see where the data is highlighted

and move the box that includes the data in K over one column from the

bottom corner where you typically grab for the fill tool).

8. Go to Sheet 2, the new table will require you find the percents for height

categories. Use absolute referencing and the SUM of total people you

calculate in cell L.4.

9. Format the numbers in row 5 to percentages with ONE decimal place

showing.

10. Insert a line graph of height frequencies, using heights as X values.

11. Title the chart "Height Levels" and create appropriate axis titles.

12. Make the line ORANGE.

13. Copy the table of heights and Frequencies only from B3 to K4 and transpose

them into cell C9.

14. Sort the table by frequency LARGEST to SMALLEST (ZA).

15. Create a 3D Column Chart of this table (where you have heights sorted largest

to smallest). Once again, appropriately label the chart, axes, and get rid of the

legend. Add Data labels to the columns that display the height category at the

top of each column only. Call this table "Heights by Frequency."

16. Position the graphs side-by-side in the excel page underneath the two tables./n17. Change the name of the tabs "Sheet 1" and "Sheet2" to "GRADES" and

"HEIGHTS" respectively.

18. The sheets North, South, East, and West have information about regional,

quarterly expenses for a company. In each of these sheets calculate the Total

S/attendee in cells B13:E13 using the given information.

19. Next, in the Summary sheet, you must aggregate all of the data and

information from sheets North, South, East, and West so the company can see

their total expenses. (Hint: Use the SUM function and reference cells from

the different sheets. Once you complete the formula in cell B3 of the

Summary sheet you should be able to fill the formula down the column

and across the rows for the other expenses).

a. This will not work for S/Attendee

b. Please SUM the above data for ROW 9 for TOTAL instead of

referencing the four tabs.

20. Save the file as "ExcelHomework3_FirstName" and submit on Brightspace.

Fig: 1

Fig: 2