Week 5 Assignment Guide

Before you can create a pie chart, you need a small summary table that counts how many donors belong to each blood type.

• On your Summary sheet (or create one), list all 8 blood types in a column: A+, A−, B+, B−, AB+, AB−, O+, O−
• In the next column, use =COUNTIF to count how many donors match each blood type from your Donor Data sheet
• The formula pattern is: =COUNTIF(DonorData!C:C, "A+") — adjust the column reference to wherever your blood type data lives

Now turn your summary table into a visual pie chart:

• Select your summary table (blood type names and their counts)
• Go to Insert tab → Chart → choose Pie
• Pick a style you like — a standard 2-D pie or a 3-D pie both work

Polish your chart so it looks board-presentation-ready:

• Title: Click the chart title and type "Donor Blood Type Distribution"
• Data Labels: Click the chart → Chart Design tab → Add Chart Element → Data Labels → choose Percentage (or "Best Fit" and then format them to show percentages)
• Colors: Avoid the default rainbow. Right-click individual slices to change colors, or use Chart Design → Change Colors to pick a professional palette

You need a small cross-tabulation table before creating the chart:

• Create a small table with 3 rows (Morning, Midday, Afternoon) and 2 columns (Successful, Deferred)
• Use =COUNTIFS to count records that match both a time slot AND a status
• The formula pattern is: =COUNTIFS(TimeSlotColumn, "Morning", StatusColumn, "Successful")
• Fill in all 6 cells (3 time slots x 2 statuses)

Turn your summary table into a visual comparison:

• Select the entire summary table (including the row and column headers)
• Go to Insert → Chart → choose Clustered Column (the first column chart option)
• You should see two bars side by side for each time slot — one for Successful, one for Deferred

The rubric specifically requires all three of these elements:

• Chart Title: Click the title area and type something descriptive (e.g., "Donation Outcomes by Time Slot")
• Axis Labels: Go to Chart Design → Add Chart Element → Axis Titles. Add both a horizontal axis title (e.g., "Time Slot") and a vertical axis title (e.g., "Number of Donors")
• Legend: Make sure the legend is visible showing "Successful" and "Deferred" — it should appear automatically, but check Add Chart Element → Legend if it is missing

Think about what would be interesting or useful for Dr. Martinez's board presentation. Here are some ideas:

• Bar chart — Donations by age group (17–25, 26–35, 36–45, 46–55, 56+). Who donated the most?
• Doughnut chart — Overall successful vs. deferred (like a pie chart with a hole in the middle)
• Stacked bar chart — Outcome (successful/deferred) broken down by blood type
• Line chart — Cumulative donations over appointment times throughout the day

Pick whichever one you find most interesting. There is no single right answer — the rubric rewards creativity and a clear visual story.

Follow the same pattern as Tasks 1 and 2:

1. Create a small summary table for your chosen data angle (use COUNTIF, COUNTIFS, or manual grouping)
2. Select the summary table and insert your chosen chart type
3. Add a descriptive title that tells the reader what the chart shows
4. Add data labels or axis labels so the values are clear

=SUMIF adds up values from one column when a condition in another column is met.

• You need three SUMIF formulas — one each for Morning, Midday, and Afternoon
• The formula pattern: =SUMIF(TimeSlotColumn, "Morning", UnitsColumn)
• Think about which column contains the time slot labels and which column contains the units collected
• Label each result clearly (e.g., "Morning Units:", "Midday Units:", "Afternoon Units:")

If you already created COUNTIF formulas for the pie chart in Task 1, you may already have this done. Verify:

• You should have 8 COUNTIF formulas — one for each blood type
• The formula pattern: =COUNTIF(BloodTypeColumn, "A+")
• Make sure all 8 counts add up to your total donor count (42)

=AVERAGEIF calculates the average of values that meet a condition.

• You need two formulas: one for the average age of successful donors and one for deferred donors
• The formula pattern: =AVERAGEIF(StatusColumn, "Successful", AgeColumn)
• Label them clearly so the grader can see both values and compare them

Think about what the difference tells you — is there an age-related pattern in deferral rates? This is the kind of insight Dr. Martinez would mention in her board presentation.

Sparklines are tiny charts inside a single cell — they show mini-trends at a glance.

• Click in an empty cell next to your summary data
• Go to Insert → Sparklines → choose Line or Column
• For Data Range, select the row or column of numbers you want to visualize (e.g., the 3 time slot counts, or the 8 blood type counts)
• For Location Range, select the cell where you want the sparkline to appear

Good sparkline candidates:

• Donations by time slot (3 values — shows the busiest period at a glance)
• Blood type counts (8 values — shows which types are most common)

Start by creating the reference table:

• Add a new sheet tab and name it "Lookup"
• In the top section, create a Blood Type Compatibility Table with three columns: Blood Type, Can Donate To, Can Receive From
• List all 8 blood types (A+, A−, B+, B−, AB+, AB−, O+, O−)
• Fill in the compatibility info for each type — you can look up blood type compatibility online or in your course materials

Below your compatibility table, create a "Donor Lookup" section:

• Create a labeled input area: a cell labeled "Enter Donor ID:" with an empty cell next to it where you will type an ID
• Below that, create result rows for: Donor Name, Blood Type, and Donation Status
• Use =VLOOKUP or =XLOOKUP in each result cell to pull from your Donor Data sheet

VLOOKUP syntax reminder:

• =VLOOKUP(InputCell, DonorData!A:F, 2, FALSE)
• The InputCell is where you type the Donor ID
• The table_array should start with the Donor ID column from your Donor Data sheet
• The col_index number determines which column to return (2 = Name, 3 = Blood Type, etc.)
• FALSE means exact match — always use FALSE for lookups

The rubric requires visible proof that your lookup works:

• Create 3 separate lookup rows (not just one cell you keep retyping)
• In each row, enter a different Donor ID from your data and let the VLOOKUP formulas pull the results
• Leave all 3 tests visible so the grader can see them without modifying your file

Start with a clean canvas:

• Add a new sheet and name it "Dashboard"
• Immediately go to View tab → uncheck Gridlines for a clean look
• Add a title at the top using merged cells and a fill color (e.g., "Sunnydale Blood Drive — Executive Summary")
• Plan your layout: key metrics at the top, summary table in the middle, charts at the bottom

Create 3 prominent metric boxes across the top of your dashboard:

• Total Donors: Use a formula that counts all records (e.g., =COUNTA on the name column, minus the header)
• Units Collected: Use =COUNTIF on the status column for "Successful"
• Success Rate: Calculate as Units Collected / Total Donors, formatted as a percentage

Make these numbers large and bold — they should be the first thing someone notices. Use merged cells, large font sizes (18-24pt), and a subtle background fill color.

Fill in the body of your dashboard:

• Summary Table: Create a blood type breakdown table showing each type, its count, and its percentage of total donors. Format with borders and alternating row colors.
• Charts: Copy (or move) at least 2 charts from your other sheets onto the Dashboard. Right-click a chart → Copy → go to the Dashboard sheet → Paste. Resize them to fit your layout.

This is where you earn the professionalism points. Go through the entire workbook:

• Sheet tabs: Name all tabs clearly (Donor Data, Summary, Charts, Lookup, Dashboard)
• Consistent formatting: Same fonts, same number formats, same color scheme across all sheets
• Spelling check: Press F7 to run spell check on every sheet — labels, titles, and notes should be error-free
• Data integrity: All formulas should reference live data. If you change a donor record, charts and metrics should update automatically
• Dashboard layout: Use borders, fill colors, and merged cells to create clear visual sections. Align charts neatly.

Before submitting, walk through each sheet and verify:

1. Pie Chart: Shows all 8 blood types, has percentage labels, professional colors, and a title
2. Column Chart: Clustered columns for Successful vs. Deferred by time slot with axis labels, title, and legend
3. Third Chart: Your creative choice — has a title and clear labels
4. Formulas: SUMIF (3 time slots), COUNTIF (8 blood types), AVERAGEIF (2 statuses), and at least one sparkline
5. Lookup Sheet: Blood type compatibility table (8 types) + VLOOKUP/XLOOKUP tool tested with 3 Donor IDs
6. Dashboard: Key metrics, summary table, 2+ embedded charts, clean layout with no gridlines
7. Sheet tabs: All named clearly
8. No spelling errors in labels or titles

Finalize your submission:

1. Save your file as LastName_Week5_BloodDrive.xlsx
2. Double-check the file name follows the required format
3. Submit through the course LMS before the deadline