Assignment on circular cell references in Excel,

Rashmi Mishra
personRashmi Mishra
October 28, 2024

 circular cell references in Excel,

Assignment 1: Monthly Interest Calculation

Objective: Create a worksheet to calculate the running balance of a savings account with monthly interest using a circular reference.

Task

  1. Create a new Excel worksheet.
  2. Set up the following columns:
    • A1: "Month"
    • B1: "Balance"
  3. In cell A2, type "1" (for January) and continue numbering down to A13 (for December).
  4. In cell B2, enter your starting balance of $1,000.
  5. In cell B3, create a formula that calculates the new balance by applying a 2% interest rate to the previous month’s balance.
  6. Copy the formula down to cell B13 to calculate the balance for each month.


Assignment 2: Running Total of Expenses

Objective: Create a worksheet to track monthly expenses where each month’s total depends on the previous month’s total.

Task

  1. Create a new Excel worksheet.
  2. Set up the following columns:
    • A1: "Month"
    • B1: "Monthly Expense"
    • C1: "Running Total"
  3. In cell A2, type "1" and continue numbering down to A13.
  4. In cell B2, enter a random expense amount (e.g., $200).
  5. In cell C2, enter the same expense as in B2.
  6. In cell B3, enter a random expense amount (e.g., $150).
  7. In cell C3, create a formula that adds the current month's expense (B3) to the previous running total (C2).
  8. Copy the formulas down to cells B4 to B13 and C4 to C13.


Assignment 3: Average Monthly Temperature Calculation

Objective: Use a circular reference to calculate the average monthly temperature based on previous months.

Task

  1. Create a new Excel worksheet.
  2. Set up the following columns:
    • A1: "Month"
    • B1: "Temperature"
    • C1: "Average Temperature"
  3. In cell A2, type "1" and continue numbering down to A13.
  4. In cell B2, enter an initial temperature (e.g., 20°C).
  5. In cell C2, enter the same initial temperature.
  6. In cell B3, enter a temperature value (e.g., 25°C).
  7. In cell C3, create a formula to calculate the average of the previous month's temperature and the current month's temperature.
  8. Copy the formulas down to cells B4 to B13 and C4 to C13.



SOLUTIONS

Assignment 1: Monthly Interest Calculation

Objective: Create a worksheet to calculate the running balance of a savings account with monthly interest using a circular reference.

Task

  1. Create a new Excel worksheet.
  2. Set up the following columns:
    • A1: "Month"
    • B1: "Balance"
  3. In cell A2, type "1" (for January) and continue numbering down to A13 (for December).
  4. In cell B2, enter your starting balance of $1,000.
  5. In cell B3, create a formula that calculates the new balance by applying a 2% interest rate to the previous month’s balance.
  6. Copy the formula down to cell B13 to calculate the balance for each month.

Solution Steps

  1. Set Up the Worksheet:
    • Open Excel and create a new worksheet.
    • In A1, type "Month" and in B1, type "Balance".
    • In A2, enter "1" and drag the fill handle down to A13 to fill in the numbers 1 to 12.
  2. Enter Starting Balance:
    • In B2, enter 1000.
  3. Create the Circular Reference Formula:
    • In B3, enter the formula:

=B2 * 1.02

    • This formula takes the previous month's balance (B2) and multiplies it by 1.02 to account for the 2% interest.
  1. Copy the Formula Down:
    • Click on B3, drag the fill handle down to B13. Excel will automatically calculate each month's balance based on the previous month's value.
  2. Final Result:
    • Your table will look like this:

Month

Balance

1

$1,000.00

2

$1,020.00

3

$1,040.40

4

$1,061.21

5

$1,082.43

6

$1,104.08

7

$1,126.12

8

$1,148.64

9

$1,171.58

10

$1,195.00

11

$1,218.90

12

$1,243.28

Assignment 2: Running Total of Expenses

Objective: Create a worksheet to track monthly expenses where each month’s total depends on the previous month’s total.

Task

  1. Create a new Excel worksheet.
  2. Set up the following columns:
    • A1: "Month"
    • B1: "Monthly Expense"
    • C1: "Running Total"
  3. In cell A2, type "1" and continue numbering down to A13.
  4. In cell B2, enter a random expense amount (e.g., $200).
  5. In cell C2, enter the same expense as in B2.
  6. In cell B3, enter a random expense amount (e.g., $150).
  7. In cell C3, create a formula that adds the current month's expense (B3) to the previous running total (C2).
  8. Copy the formulas down to cells B4 to B13 and C4 to C13.

Solution Steps

  1. Set Up the Worksheet:
    • In A1, type "Month", in B1, type "Monthly Expense", and in C1, type "Running Total".
    • Fill in the months from A2 to A13.
  2. Enter Monthly Expenses:
    • In B2, enter 200.
    • In B3, enter 150.
  3. Set Up Running Total:
    • In C2, enter:

=B2

    • This initializes the running total with the first month’s expense.
  1. Create Circular Reference Formula:
    • In C3, enter:

=C2 + B3

    • This adds the current month’s expense (B3) to the running total of the previous month (C2).
  1. Copy the Formulas Down:
    • Fill in expenses randomly for B4 to B13 (e.g., $300, $250, etc.).
    • Copy the formulas in C2 and C3 down to C4 to C13.
  2. Final Result:
    • Your table might look like this:

Month

Monthly Expense

Running Total

1

$200

$200

2

$150

$350

3

$300

$650

4

$250

$900

5

$100

$1000

6

$400

$1400

7

$50

$1450

8

$300

$1750

9

$200

$1950

10

$150

$2100

11

$100

$2200

12

$300

$2500

Assignment 3: Average Monthly Temperature Calculation

Objective: Use a circular reference to calculate the average monthly temperature based on previous months.

Task

  1. Create a new Excel worksheet.
  2. Set up the following columns:
    • A1: "Month"
    • B1: "Temperature"
    • C1: "Average Temperature"
  3. In cell A2, type "1" and continue numbering down to A13.
  4. In cell B2, enter an initial temperature (e.g., 20°C).
  5. In cell C2, enter the same initial temperature.
  6. In cell B3, enter a temperature value (e.g., 25°C).
  7. In cell C3, create a formula to calculate the average of the previous month's temperature and the current month's temperature.
  8. Copy the formulas down to cells B4 to B13 and C4 to C13.

Solution Steps

  1. Set Up the Worksheet:
    • Type the column headers in A1, B1, and C1.
    • Fill in the months from A2 to A13.
  2. Enter Initial Temperature:
    • In B2, enter 20.
    • In C2, enter:

=B2

  1. Enter Temperature for Subsequent Months:
    • In B3, enter 25.
  2. Set Up Average Calculation:
    • In C3, enter the formula:

=(B2 + B3) / 2

  1. Copy Formulas Down:
    • Enter different temperatures in B4 to B13.
    • Copy the formulas in C3 down to C4 to C13.
  2. Final Result:
    • Your table might look like this:

Month

Temperature

Average Temperature

1

20

20

2

25

22.5

3

22

23.5

4

18

20

5

21

19.5

6

30

25

7

35

27.5

8

33

29.5

9

25

29

10

20

24

11

15

20

12

10

17.5

Key Points for Students

  1. Understanding Circular References: A circular reference occurs when a formula refers back to its own cell or another cell that depends on it. This can be useful for running totals, averages, and interest calculations.
  2. Enabling Iterative Calculation: It is important to enable iterative calculations in Excel for circular references to function correctly.
  3. Step-by-Step Approach: Break down tasks into small, manageable steps to help understand how formulas work and how changes in one cell can affect others.

By working through these assignments, students will gain hands-on experience with circular references and better understand how they can be applied in real-world scenarios.


Tags

You Might Like

Show more
cell references
Share to other apps
Copy Post link