CS 115 The Power of Excel Replication

CS115 Extra Credit Assignment

The grade on this extra credit assignment will replace your lowest of:

Birthdays

Objectives

What do you think the odds are against two people in the class having the same birthday? There are 366 days per year on which a birthday can occur. First, assume there are two people in the class: you and someone else. The probability that two people (you and the other person) do not have the same birthday is 365/366 (their birthday is on one of the other 365 days of the year). The probability that you both have the same birthday is 1-365/366 (1 represents 100% and you subtract the probability that the person does not have the same birthday as you from 100% to obtain the probability that they do have the same birthday as you.

If there are 3 people in the class, the probability that two of the people do not have the same birthday is (364/366)*(365/366), 364 other days*366 total days * the probability that 2 people have different birthdays. The probability that three do have the same birthday is 1-(364/366*365/366).

If there are 4 people in the class, the probability that two people having the same birthday becomes (363/366)*(364/366)*(365/366) and the probability that two people have the same birthday becomes 1-((363/366)*(364/366)*(365/366) ).

Do you get the idea!!!!!

Create a spreadsheet that gives the probability of different birthdays and the probability of the same birthday for 2-50 people.

Specifications

  1. Row 1 Column A should contain a title in bold and font size of 16.
  2. Row 2 Column A should contain your name.
  3. Row 3 should contain headings. They should be in bold and size 12. Use alt-enter to make the headings multiple lines. Column A should have the heading Number of People. Column B should have the heading Probability of Different Birthdays. Column C should have the heading Probability of the Same Birthday.
  4. Rows 4-52 Column A should contain the values 2 through 50. You are not to type these values in. Let Excel do the work for you. In Row 4 Column A, type the value 2. In Row 5 Column A, type the value 3. Mark Rows 4 and 5 Column A. Replicate the data down through Row 52. Excel will fill in the values 4-50 for you.
  5. Row 4 Column B should contain the formula as described above that 2 people have different birthdays. The formula is =365/366. The formula for B5 will be different, so this formula will not be replicated.
  6. Row 4 Column C should contain the formula as described above that 2 people have the same birthday. The formula is =1-b4.
  7. The formula for C4 can be replicated down through all of the rows. Until the formula for Column B5 is written and replicated, the data in Column C will be invalid.
  8. Write a formula for B5 that uses A4 and B4. It should calculate the probability that for 3 people in the class, 2 do not have the same birthday. This formula should be written so that it can be replicated through the rest of the rows for corresponding number of people in the class that two people do not have the same birthday.

    To develop the formula, examine the pattern for calculating the number of people that do not have the same birthday. Three people: (364/366)*(365/366). Four people: (363/366)*(364/366)*(365/366).

    A4 contains the number of people to be subtracted from 366 in the first factor of the formula and B4 contains the second factor of the formula.

    The formula should be: =(366-A4)/366*B4

    Replicate the formula. As the formula is replicated down, the number of terms in the second factor increases.

  9. Mark the data in B4:C4. Format the data as a percentage with 4 decimal places.
  10. Check your results

Completion

Save your file in a spreadsheet called birthdays.

Submit your spreadsheet in D2L. If you submit any type of file other than a spreadsheet, it will not be accepted. You will not have the opportunity to resubmit it.

Link to the results
This lab is © copyright protected by Barbara Bracken

This page is maintained by Barbara Bracken Last Modified

10/23/2025